Forum Discussion
Total not match with value
I've list in table visual
the actual total is 90 wheras its showing 100 due to i created measures.
if Banana then take value from different column and rest are same so i assign measure so its working all rows as total not giving right value as 90. how do we create measures in "Total" dynamic values based all rows total.
Desc Quarter1
Apple 10
Banana 20
orange 20
Mango 40
Total 100
Hi Anonymous ,
Base on your description, it seems like you created the following measure. However, the total value is not correct.
VAR _period = SELECTEDVALUE('Calendar'[Qtr-Yr]) RETURN SWITCH( TRUE(), SELECTEDVALUE('Region Table'[Region])="Denmark", CALCULATE(SUM( 'Data'[Value]), ALLEXCEPT('Calendar','Calendar'[Date]), 'Data'[Acct FQ]=_period), CALCULATE(SUM( 'Data'[Value]), ALLEXCEPT('Calendar','Calendar'[Date]), 'Data'[Revenue FQ]=_period))You can create another new measure as below, and put this new measure to replace your above measure.
New Measure = SUMX ( VALUES ( 'Data'[Desc] ), [your original measure] )In addition, you can refer the following link to try to solve your problem...
Dax for Power BI: Fixing Incorrect Measure Totals
If the above one can't help you figure out, please provide some raw data in your tables(exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:
How to provide sample data in the Power BI Forum
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi,
This measure pattern should work
Measure = sumx(values(Data[Desc]),[Quarter1])
Hope this helps.
8 Replies
- bhanu_gautam
Super User
Anonymous , Go through this post from Greg_Deckler
https://community.fabric.microsoft.com/t5/Desktop/Measure-Total-Incorrect/m-p/4396734#M1369884
- Greg_Deckler
Community Champion
Thanks bhanu_gautam ! Also, don't forget to vote for the idea, especially if it solves your problem!
First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
mohammedismailHelper I
Here is a video explaining why the Totals are incorrect and how to fix them!
Why Incorrect Totals in Power BI ? How to Fix Incorrect totals | MiTutorials | #PowerBI
Create a dummy table i.e Test with below column
Desc
Apple
Banana
orange
Mango
Total
Create a measure
M1= Switch(SelectedValue([Test]Desc),[yourTablename]Desc, SUM([yourTablename]Quarter,SUM([yourTablename]Quarter)
add [Test]Desc and M1 in tableHope this helps
This is Measure applied as "Total" not giving right value as changed summarize /If function as doesn't works
Pls support here.
VAR _period = SELECTEDVALUE('Calendar'[Qtr-Yr])
RETURN
SWITCH(
TRUE(),SELECTEDVALUE('Region Table'[Region])="Denmark",
CALCULATE(SUM( 'Data'[Value]),
ALLEXCEPT('Calendar','Calendar'[Date]),
'Data'[Acct FQ]=_period),CALCULATE(SUM( 'Data'[Value]),
ALLEXCEPT('Calendar','Calendar'[Date]),
'Data'[Revenue FQ]=_period))
This is Measure applied as "Total" not giving right value as changed summarize /If function as doesn't works
Pls support here.
VAR _period = SELECTEDVALUE('Calendar'[Qtr-Yr])
RETURN
SWITCH(
TRUE(),SELECTEDVALUE('Region Table'[Region])="Denmark",
CALCULATE(SUM( 'Data'[Value]),
ALLEXCEPT('Calendar','Calendar'[Date]),
'Data'[Acct FQ]=_period),CALCULATE(SUM( 'Data'[Value]),
ALLEXCEPT('Calendar','Calendar'[Date]),
'Data'[Revenue FQ]=_period))Hi Anonymous ,
Base on your description, it seems like you created the following measure. However, the total value is not correct.
VAR _period = SELECTEDVALUE('Calendar'[Qtr-Yr]) RETURN SWITCH( TRUE(), SELECTEDVALUE('Region Table'[Region])="Denmark", CALCULATE(SUM( 'Data'[Value]), ALLEXCEPT('Calendar','Calendar'[Date]), 'Data'[Acct FQ]=_period), CALCULATE(SUM( 'Data'[Value]), ALLEXCEPT('Calendar','Calendar'[Date]), 'Data'[Revenue FQ]=_period))You can create another new measure as below, and put this new measure to replace your above measure.
New Measure = SUMX ( VALUES ( 'Data'[Desc] ), [your original measure] )In addition, you can refer the following link to try to solve your problem...
Dax for Power BI: Fixing Incorrect Measure Totals
If the above one can't help you figure out, please provide some raw data in your tables(exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:
How to provide sample data in the Power BI Forum
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
- Ashish_Mathur
Hi,
This measure pattern should work
Measure = sumx(values(Data[Desc]),[Quarter1])
Hope this helps.
