Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I hope someone will have an idea what is happening here.
I have this beatutiful formula that works, mostly, except I am not sure how it sums up the totals.
The total should be 1.44. It doesn't make any sense why and how it gets 1.22
In my table:
Rows: Connector & Calendar Table
Columns: Charger Type
Values: Average Connector Usage formula (below)
Average Connector Usage = IF ( NOT ISEMPTY ( 'FACT TABLE' ), VAR minDate = MIN ( 'FACT TABLE'[Start Date] ) VAR maxDate = MAX ( 'FACT TABLE'[Start Date] ) RETURN CALCULATE ( AVERAGEX ( VALUES ( 'Calendar'[Date] ), COALESCE ( [SDR ID average per CP ID], 0 ) ), DATESBETWEEN ( 'Calendar'[Date], minDate, maxDate ) ) )
Many thanks,
J
Hi @Anonymous ,
The total row is not just row1 result + row2 result+..., it is the all the table's context for the measure to calcualte. And the 1.22 is average of all the fact table.
If you want sum (0.19, 0.46,0.79) , you can create use HASONVALUE(), like the following:
Average Connector Usage =
IF (
HASONEVALUE ( 'fact table'[connector] ),
IF (
NOT ISEMPTY ( 'FACT TABLE' ),
VAR minDate =
MIN ( 'FACT TABLE'[Start Date] )
VAR maxDate =
MAX ( 'FACT TABLE'[Start Date] )
RETURN
CALCULATE (
AVERAGEX (
VALUES ( 'Calendar'[Date] ),
COALESCE ( [SDR ID average per CP ID], 0 )
),
DATESBETWEEN ( 'Calendar'[Date], minDate, maxDate )
)
),
SUMX (
SUMMARIZE (
'fact table',
[connector],
"a",
VAR minDate =
MIN ( 'FACT TABLE'[Start Date] )
VAR maxDate =
MAX ( 'FACT TABLE'[Start Date] )
RETURN
CALCULATE (
AVERAGEX (
VALUES ( 'Calendar'[Date] ),
COALESCE ( [SDR ID average per CP ID], 0 )
),
DATESBETWEEN ( 'Calendar'[Date], minDate, maxDate )
)
),
[a]
)
)
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please see if you can come up with an alternative solution.
@Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
User | Count |
---|---|
54 | |
46 | |
19 | |
16 | |
15 |
User | Count |
---|---|
115 | |
42 | |
41 | |
26 | |
22 |