Advanced › HASONEVALUE, DIVIDE46 / 50

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeHASONEVALUEDIVIDE

HASONEVALUE: payroll vs budget ratio

Mrs. Lestari compares each department's annual payroll with its annual budget (Departments[Budget]). The budget is a per-department number, so the ratio only makes sense when exactly one department is in view. The measure [Monthly Payroll] (total monthly salaries) already exists.

Task

Create the measure Rasio Gaji vs Anggaran (payroll vs budget ratio) in the Employees table: when HASONEVALUE ( Departments[Department] ) is TRUE, return [Monthly Payroll] × 12 divided by that department's budget (VALUES ( Departments[Budget] ), using DIVIDE); otherwise return BLANK (an IF without a third argument is enough).
Visual: department (Departments[Department]).

MeasureRasio Gaji vs Anggaranin table Employees

Required functionsHASONEVALUEDIVIDE

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Run previewCtrlEnter
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
VALUES ( column ) can be used as a single value only when it holds exactly one row. The Total row has 6 departments, so that expression fails there.
Hint 2
HASONEVALUE checks whether a column has exactly one value in the filter context. Wrap the calculation in IF ( HASONEVALUE ( ... ), ... ).
Hint 3
IF ( HASONEVALUE ( Departments[Department] ), DIVIDE ( ___, VALUES ( Departments[Budget] ) ) )

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

Rasio Gaji vs Anggaran

Rasio Gaji vs Anggaran = IF (
    HASONEVALUE ( Departments[Department] ),
    DIVIDE ( [Monthly Payroll] * 12, VALUES ( Departments[Budget] ) )
)

Explanation

In a department row HASONEVALUE is TRUE and VALUES ( Departments[Budget] ) holds a single number, so the ratio can be computed: Penjualan (sales) spends 46% of its budget on payroll, while Teknologi Informasi (IT) spends only 25%. The Total row has 6 departments; without HASONEVALUE, VALUES would fail ("a table of multiple values was supplied where a single value was expected") and the visual would not render. With IF, the total is left blank.

Why not SUM ( Departments[Budget] )? The Total row would then show a combined ratio of 33%, which is easily misread as a benchmark for every department. This exercise deliberately leaves the total blank.
SELECTEDVALUE ( Departments[Budget] ) is a compact alternative to the IF + HASONEVALUE + VALUES pattern: it returns BLANK when there is more than one value, and DIVIDE by a BLANK denominator returns BLANK too.

Data model: hr· table Employees

Rasio Gaji vs Anggaran =

Loading the DAX editor…

Write the DAX expression, then press Check answer.

Validation result