Forum Discussion
Dax measure doesn't return correct total value in all rows
If I understood correctly now:
We calculate Rate separately for the rows and for the Total. For each row we have one related utility, for the total we calculate average of averages for all selected utilities:
VAR IsOneUtility = HASONEVALUE ( 'Points'[Utility] ) VAR UtilityAverage = IF ( IsOneUtility, CALCULATE ( [Invoice Unit Rate test 2], ALL ( 'Points'[DBName-Point_Id] ), 'Points'[Utility] = VALUES ( 'Points'[Utility] ) ) ) VAR TotalAverage = CALCULATE ( AVERAGEX ( VALUES ( 'Points'[Utility] ), [Invoice Unit Rate test 2] ), ALLSELECTED ( 'Points'[DBName-Point_Id] ) ) VAR Result = IF ( IsOneUtility, UtilityAverage, TotalAverage ) RETURN Resultwhich gives
Just in case,
Invoice Unit Rate test 2 = VAR Cost = SUM ( 'Invoice Data'[Cost] ) VAR Units = SUM ( 'Invoice Data'[Units] ) VAR div = DIVIDE ( ABS ( Cost ), ABS ( Units ), BLANK () ) RETURN CALCULATE ( IF ( ISBLANK ( Cost ) || ISBLANK ( Units ), BLANK (), div ) )
I checked only second one.
For DBName-Point_Id you'd like to calculate total for only only two selected points, not for all existing ones. Thus
Invoice Unit Rate test 3 =
CALCULATE (
[Invoice Unit Rate test 2],
// ALL ( Points[DBName-Point_Id] )
ALLSELECTED ( Points[DBName-Point_Id] )
)With previous measure you don't need ALL(Points) since Cost and Units are calculated in current row context. If to remove
Invoice Unit Rate test 2 =
VAR Cost = SUM ( 'Invoice Data'[Cost] )
VAR Units = SUM ( 'Invoice Data'[Units] )
VAR div =
CALCULATE ( DIVIDE ( ABS ( Cost ), ABS ( Units ), BLANK () ) ) //,ALL(Points))
RETURN
CALCULATE (
IF ( ISBLANK ( Cost ) || ISBLANK ( Units ), BLANK (), div ),
ALL ( 'Invoice Data' ),
ALL ( 'Calendar' ),
ALL ( Contacts )
)
//,ALL(Points))result will be the same.
Finally
Hi SergeiBaklan,
Apologise for the delay in getting back as we were testing this measure at different levels!
Many thanks for your amazing solution Sir!
What we found from our testing was as below:
This measure worked amazingly well when we unselect points id and the values are also correct:
Dax:
Result:
When I unselect points, and select either 1 Utility(gas), this shows correct value 0.36:
When i select electricity utility,dax shows 0.15 for all points which is correct:
FYR, some examples of point id's that comes under electricity & gas utility as below for your testing purposes:
The Electric Utility contains below points:
INSE-1000
INSE-1001
INSE-1002
INSE-1003
INSE-998
INSE-324
INSE-41
The gas utility contains points id as below:
INSE-1214
INSE-1215
INSE-1308
Now when I select both utility, it shows wrong value(0.16) for all points as shown below based on utility selected:
When i select both utility, dax should show their corresponding average for their points, (i.e)
The gas utility points should show, 0.36(overall for all gas points) & The Electricity utility points should show 0.15 (overall for all electricity points)
Expected outcome for example point id given above:
This should work when we unselect/show all point id (0.36 for gas & 0.15 for electricity utility) with only 2 utility selected and expected result as below:
PFA file in link https://1drv.ms/u/s!Au2HPfdnp63PeTvG9U6d3zkb9Y8?e=1Am0SP
Thanks in advance!
Perhaps better to modify test3
Invoice Unit Rate test 3 = VAR test2 = [Invoice Unit Rate test 2] RETURN CALCULATE ( test2, // [Invoice Unit Rate test 2], ALLSELECTED ( Points[DBName-Point_Id] ) )With that if [Invoice Unit Rate test 2] is blank, [Invoice Unit Rate test 3] is also returns blank and no need to apply filter to visual
Hi SergeiBaklan
Thanks for your quick response!
Apologise for not giving the clear explanation!
My expected result is as below:
When I select the utility as gas from utility slicer, this measure in below table should show 0.36 for DBName-Poind_id column
and 0.15 when we select Electricity.
We don't have to worry about Invoice Unit Rate test 2 measure showing blank values/cells and neither for Invoice Unit Rate test 3.
I am just confused how/whether to modify Invoice Unit Rate test 3 or Invoice Unit Rate test 2 measure in order to achieve above?
Can you please help achieve this result?
PFA file here https://1drv.ms/u/s!Au2HPfdnp63PeTvG9U6d3zkb9Y8?e=sgWEdC
Thanks in advance!
Hi SergeiBaklan ,
Adding to above,
We need both measures:
Invoice Unit Rate test 2 that calculates the average of every DBName_Point Id as shown below:There is no problem with above measure, so please ignore it.
Now we need to work/modify on 'Invoice Unit Rate test 3' measure to make sure it calculates the average of their DBName_Point Id under their respective Utility(Electricity or Gas).
'Invoice Unit Rate test 3' measure achieves that when we select either 'Electricity' (0.15) only or 'Gas'(0.36) only from slicer. These numbers(0.15 & 0.36) are nothing but average of point id under their respective utility(Electricity/Gas).
But 'Invoice Unit Rate test 3' does not achieve that when we select both utlity in slicer as below:
It shows only 0.16 in every cell and total cell of the measure column.
The above screenshot shows only two values(0.15 & 0.36) in every cell which is average of gas & electricity utility. The total shows average of 0.15 & 0.36 which is 0.25.For example in below screenshot
Green color highlight shows 0.15 as average for point id's that comes under electricity utility and 0.36 as average for point id's that comes under gas utility.
Please let me know if you need further information.
PFA file here https://1drv.ms/u/s!Au2HPfdnp63PeTvG9U6d3zkb9Y8?e=43QYfn
Thanks in advance!
If to show records when both measures are not blank, you may apply filter to the visual
which gives