Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
sivarajan21
Post Patron
Post Patron

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

Hi,

 

Created a measure as below:

sivarajan21_0-1722933753484.png

&

sivarajan21_1-1722933781729.png

 

But the above measures doesn't return correct values and it is not matching my expected result.

My expected result is:

sivarajan21_3-1722934018218.png

 

 

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 @v-linyulu-msft @marcorusso @jgeddes @amitchandak @v-yaningy-msft @Greg_Deckler 

1 ACCEPTED SOLUTION

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_0-1722944121063.png

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

View solution in original post

8 REPLIES 8
Thennarasu_R
Responsive Resident
Responsive Resident

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
Resident Rockstar
Resident Rockstar

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.

SamWiseOwl_0-1722935508535.png

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:

SamWiseOwl_1-1722935633389.png

I will have a look at Invoice test 2


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

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:

sivarajan21_0-1722936363066.png

 

Can you please help me achieve this?

Please let me know if you need further info!

 

Thanks in advance!

@SamWiseOwl @v-linyulu-msft @v-yaningy-msft @Ahmedx @Greg_Deckler @jgeddes @marcorusso 

Hi @sivarajan21 

What is Test 3 doing/ meant to be doing?

Test 2 is working fine here:

SamWiseOwl_0-1722937128486.png

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

)

If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

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.

If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

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:

sivarajan21_2-1722940393364.png

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:

sivarajan21_3-1722941676821.png

It shows only 0.16 in every cell and total cell of the measure column. 

 

My expected output is as below:

sivarajan21_4-1722941704661.png

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

sivarajan21_5-1722941970604.png

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 @v-linyulu-msft @v-yaningy-msft @Ahmedx @marcorusso @Greg_Deckler 

 

 

 

 

 

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_0-1722944121063.png

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Hi @SamWiseOwl  ,

 

This time we did it!

Thanks for your quick response & amazing solution!

 

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

I have given kudos as well!

 

Many Thanks 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.