Forum Discussion

sivarajan21's avatar
sivarajan21
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

Dax measure doesn't return correct total value for selected slicer

Hi,   Created a measure as below: &   But the above measures doesn't return correct values and it is not matching my expected result. My expected result is:     When I sele...
data protection
General Comment
Interesting Links
Need Help
Tips and Tricks
Tutorial Requests
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    Hi sivarajan21 
    So on the utility you want their average, on the total you want to calculate the average for each utilities average?

    Invoice Unit Rate test Sam =
    If(
        HASONEVALUE(Points[Utility])
        ,CALCULATE([Invoice Unit Rate test 2], all(Points), Points[Utility] = SELECTEDVALUE(Points[Utility]))
        ,AVERAGEX(DISTINCT(Points[Utility]), [Invoice Unit Rate test 2])
        )
     

     

SamWiseOwl's avatar
SamWiseOwl
Icon for Super User rankSuper User
2 years ago

Hi sivarajan21 

Be careful of using variables in the first part of Calculate.

Variables hold the RESULT i.e the answer/number of the calculation. This means they are not recalculated when you apply filters with DAX.

You will need to put the calculation in if you want to modify the filters.

 

In your example you have both Elec and Gas ticked, did you mean this? When ticked individually Rate test 3 seems to return what you want:

I will have a look at Invoice test 2

sivarajan21's avatar
sivarajan21
Icon for Post Prodigy rankPost Prodigy
2 years ago

Hi SamWiseOwl 

 

Thanks for your quick response!

I will note your answer regarding variables and thanks for this info!

To answer your question 'In your example you have both Elec and Gas ticked, did you mean this? '

I ticked both. Yes when ticked individually it works and we knew that!

 

We want this to work, when both(Electricity & Gas) are ticked in slicer.

My expected output would be as below:

 

Can you please help me achieve this?

Please let me know if you need further info!

 

Thanks in advance!

SamWiseOwl Anonymous Anonymous Ahmedx Greg_Deckler jgeddes marcorusso 

  • SamWiseOwl's avatar
    SamWiseOwl
    Icon for Super User rankSuper User
    2 years ago

    Hi sivarajan21 

    What is Test 3 doing/ meant to be doing?

    Test 2 is working fine here:

    Invoice Unit Rate test 2 =
    VAR Cost = SUM('Invoice Data'[Cost])
    VAR Units = SUM('Invoice Data'[Units])
    VAR div = CALCULATE(DIVIDE(ABS(SUM('Invoice Data'[Cost])), ABS(SUM('Invoice Data'[Units])), BLANK()))//,ALL(Points))
    RETURN
    If(
        ISBLANK(Cost) || ISBLANK(Units)
        ,BLANK()
        ,DIVIDE(SUM('Invoice Data'[Cost]), SUM('Invoice Data'[Units]), BLANK())//,ALL(Points))
           

    )
    • SamWiseOwl's avatar
      SamWiseOwl
      Icon for Super User rankSuper User
      2 years ago

      If what you are trying to do is if the row would be empty return the overall calculation then it would be this:

      Invoice Unit Rate test 3 b =
      VAR Cost = SUM('Invoice Data'[Cost])
      VAR Units = SUM('Invoice Data'[Units])
      VAR div =
      If(
          ISBLANK(Cost) || ISBLANK(Units)
          ,BLANK()
          ,DIVIDE(SUM('Invoice Data'[Cost]), SUM('Invoice Data'[Units]), BLANK())//,ALL(Points))
             

      )
      RETURN
      IF(
          ISBLANK(div)
          ,CALCULATE(DIVIDE(SUM('Invoice Data'[Cost]), SUM('Invoice Data'[Units]), BLANK()),ALLSELECTED(Points[DBName-Point_Id]))
          ,div
      )
       
      Let me know what Test 3 is meant to be doing and I will try to help.
      • sivarajan21's avatar
        sivarajan21
        Icon for Post Prodigy rankPost Prodigy
        2 years ago

        Hi SamWiseOwl ,

         

        Thanks for your quick response!

        Apologise for the confusion here!

         

        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. 

         

        My expected output is as below:

        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 Financial Management -Tanvi Copy 3 (3) (2) - Copy - Copy.pbix

         

        Thanks in advance!

        SamWiseOwl v-yinliw-msft Anonymous Anonymous Ahmedx marcorusso Greg_Deckler