Forum Discussion
Using SELECTEDVALUE but getting no grand total
Good morning! I've got a table where I am trying to find the total purchase value of an account only after the activation of a new product. I started by adding a calculated column to the table but I would like the ability to look at it with a page filter where it stops on a specific date and it seems like with the calculated column, I can't do that.
So, I created a measure and I have put multiple VAR statements into the measure to get the selected values but when I input them into the measure I get accurate information in the row but no Grand Total unless I filter my data to a specific account (which is also not the correct total). Here is the statement and a few screen shots to show you what my results are...
Filtered list with incorrect grand total (Correct value from calculated column in blue / Incorrect value in Yellow)
Help?
7 Replies
ciken SELECTEDVALUE returns BLANK if more than one value is found (like what would happen in a Total row where the context is ALL/ALLSELECTED. 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/n4TYhF2ARe8Thanks Greg_Deckler ! My issue with HASONEVALUE is that there may be multiple accounts with the same parentid so it won't have only one value. It could be that the account with an activation has all of their orders purchased from another location, so I need those to be calculated in this row...but only once in the final. When I was using the calculated column, I created a measure like this:
SUMX(VALUES(Equipment_in_Practice[ParentID]),VAR __account = CALCULATE(MAX(Equipment_in_Practice[Product Purchased]),ALLEXCEPT(Equipment_in_Practice,Equipment_in_Practice[ParentID]))RETURN__account)Which found the max value only once. But, I'm realizing that isn't right. Because they may activate different accounts at different times which would change the purchase value (see screenshot). Three accounts activated at different times have different totals. It should still be 33859 at the grand total but each line would be different.Does that help clarify at all?
ciken I would HASONEVALUE Account and SUMMARIZE by Account. You need to essentially create an identical table in memory with SUMMARIZE as to what the visual shows.
