Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a conditional measure that is showing the wrong subtotals and totals in a matrix visualization.
The measure works like this:
If a worker is a contractor then I have to add all the hours they logged in Schedule. If the worker is not a contractor then I have to take the Defined Month Hours from the Calendar table. See example formula below:
Month Hours =IF(MAX(WorkerData[Type])="Non Contractor", SUM('Calendar'[Defined Month Hours]),CALCULATE(SUM(Schedule[HOURS]))
If I check each worker everything works correctly, but when checking the totals or subtotals, only Non Contractors are being added.
See example:
Boss | Worker | Month Hours |
Boss Subtotal | 100 | |
Worker 1 (Non contractor) | 50 | |
Worker 2 (Non contractor) | 50 | |
Worker 3 (Contractor) | 5 | |
Total | 100 |
I would appreciate any help with the issue was I have been finding solutions but not for this specific type of scenario.
Thanks.
Solved! Go to Solution.
Please see this video for how to solve this. You likely need to use something like this (replacing T1 with your actual table name.
NewMeasure = SUMX(Summarize(T1, T1[Boss], T1[Worker]), [YourMeasure])
(2) Power BI - Tales from the front #01 - Getting the Right Total - YouTube
Pat
Hi,
Does this measure work?
Measure = SUMX(VALUES(WorkerData[Type]),[Month hours])
Please see this video for how to solve this. You likely need to use something like this (replacing T1 with your actual table name.
NewMeasure = SUMX(Summarize(T1, T1[Boss], T1[Worker]), [YourMeasure])
(2) Power BI - Tales from the front #01 - Getting the Right Total - YouTube
Pat
I am afraid that the boss and the worker are at different tables.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |