Using SELECTEDVALUE but getting no grand total

[ciken]

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

Product Purchased (TEST) =

VAR ParentID = SELECTEDVALUE(Equipment_in_Practice[ParentID])

VAR InstallDate = SELECTEDVALUE(Equipment_in_Practice[Installation Date])-15

VAR ProductPurchased = SUMX(FILTER('Order Product', 'Order Product'[ParentID]=ParentID&&'Order Product'[Ship Date]>=InstallDate),CALCULATE([Total Price],'Order'[Status]<>"VOIDED"))

RETURN

ProductPurchased

Full account list with no grand total 

Filtered list with incorrect grand total (Correct value from calculated column in blue / Incorrect value in Yellow)

 Help?

[Greg_Deckler]

@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/n4TYhF2ARe8

[ciken]

Thanks @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? 

[Greg_Deckler]

@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.

[ciken]

Appreciate your help @Greg_Deckler , As an early user do you think you can help me understand how to write that? I am sure it's a simple fix, but I'm still navigating and don't know where I'd use HASONEVALUE or SUMMARIZE!

Thank you in advance,

Corey

[Greg_Deckler]

@ciken Try this and see if it works. Have to guess because not sure where Account Name comes from:

Measure = SUMX( SUMMARIZE( 'Table', [Account Name], "__Measure", [Product Purchased (TEST)] ), [__Measure] )

[ciken]

@Greg_Deckler , If I use the [Account Name] field it then counts the revenue within every single account where I only want it to be the MAX for that group. But, if I change it to the [Parent Account] then I get some super big numbers in my totals column...

Your measure with Account ID

Product Purchased (Measure) =

SUMX( SUMMARIZE( 'Equipment_in_Practice', [Account_Name__c], "__Measure", [Product Purchased (TEST)] ),[Product Purchased (TEST)])

Result:

 

Your Measure with Parent ID

Product Purchased (Measure) =

SUMX( SUMMARIZE( 'Equipment_in_Practice', [Parent Account Name], "__Measure", [Product Purchased (TEST)] ),[Product Purchased (TEST)])

[Greg_Deckler]

@ciken It's difficult or impossible to know exactly what you need/exactly what the solution is without having sample data to test with. If you can provide me that, with expected output, then I can probably, almost certainly, get you there.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.