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 select the utility as gas from utility slicer, my measure should show 0.36 in table 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 Financial Management -Tanvi Copy 3 (3) (2) - Copy - Copy.pbix

 

Thanks in advance!

Ahmedx Anonymous marcorusso jgeddes amitchandak Anonymous Greg_Deckler 

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

     

8 Replies

  • Thennarasu_R's avatar
    Thennarasu_R
    Icon for Responsive Resident rankResponsive Resident
    2 years ago

    Hi sivarajan21 

    Yes, In table visuals some time total getting wrong so on that time you should create one new measure with your base measure (Invoice unit rate)

    Use this measure for your scenarios

    Measure=

    IF
           (HASONEFILTER(DB NAME Point id column),Invoice unit rate (measure),
           SUMX(VALUES(DB NAME Point id column)),Invoice unit rate (measure)))

    Thanks,
    Thennarasu R
  • 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))
               

        )