Forum Discussion

Excellove15's avatar
Excellove15
Iron Contributor
Jul 29, 2024
Solved

Dax measure doesn't return correct total value in all rows

Hi,   Created a measure as below:   Its giving me the correct values at filter context and total as well as shown below:   My expected outcome of this measure should be: total 0.17...
BI & Data Analysis
developer
excel
Excel for web
Formulas and Functions
Macros and VBA
office 365
power bi
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 06, 2024

    Excellove15 

    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
    Result

    which 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 ) )
Excellove15's avatar
Excellove15
Iron Contributor
Aug 06, 2024

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!

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 06, 2024

Excellove15 

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
    Result

which 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 ) )
  • Excellove15's avatar
    Excellove15
    Iron Contributor
    Aug 07, 2024

    Hi SergeiBaklan ,

     

    This time we did it!

    Thanks for your quick response & amazing solution!

     

    This worked like a gem and i will mark it as best response!

    I have given kudos as well!

     

    Many Thanks 

Resources