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 should be in all rows of DBName-point id(filter context) and not their individual values(for ex: INSE-1000 should have 0.17 instead of 0.12)

 

In order to achieve that,i created a measure as below:

 

But then it gives 0.155 in all rows rather than 0.17


To elaborate with another example:

Please find the below sample data link:

https://1drv.ms/x/s!Au2HPfdnp63Pd8L2NuC-TXQ91DI?e=GrGswE

 

Below screenshot shows, dax measure '

Invoice Unit Rate test 2 ' total as  0.12620 Which is correct:

 

I wrote a measure 'Invoice Unit Rate test 3' as below to display the total of 'Invoice Unit Rate test 2' in all rows:

 

But my result is as below:

 

 

I expect Invoice Unit Rate test 3 dax measure to show values as below:
 
DBName-Point_IdInvoice Unit Rate test 2'Invoice Unit Rate test 3
INSE-10010.110780.1262
INSE-10040.141930.1262
Total0.12620.1262

 

Please advise!

PFA file here https://1drv.ms/u/s!Au2HPfdnp63PdsqSTetwvyP19Mk?e=cMXn4j

SergeiBaklan 

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 ) )

9 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 30, 2024

    Excellove15 

    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

    • Excellove15's avatar
      Excellove15
      Iron Contributor
      Aug 05, 2024

      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!

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Aug 05, 2024

        Excellove15 

        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

Resources