cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jjkmd Frequent Visitor
Frequent Visitor

adding up all values in a previous calculation

I'm trying to do a series of calculations that I won't go into at the moment.

I'm starting with a column "Charges90Rule"

Then create a measure

Charges All Partners 90% Rule = 
CALCULATE(sum(Monthly[Charges90Rule]),All(Monthly[Provider]),all(Provider),Monthly[Partner Status]="Partner")

This works to put the sum of "charges90Rule" in each and every row in the column.

 

Annotation 2019-07-22 222050.jpg

Then I do a few more calculations, and arrive at the measure "Average Charges per partner 90 Capped".  When I try to do the same adding up as I did before...

Charges All Partners 90% Rule Capped = 
CALCULATE([Average charges per partner 90 Capped],All(Monthly[Provider]),all(Provider),Monthly[Partner Status]="Partner")
I don't get something that adds up all the values - I get something in the 600k range when it should be in the high 3M range.  Seemingly same formula.  Is there some filter/summarize/all/values command I can mix in there to make this work?
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: adding up all values in a previous calculation

@jjkmd I don't know how your tables are set up, why you are doing ALL (monthly[provider] and All(Provider)

 

Looks like you have provider table linked with monthly table.

 

Try following measure to sum up average

 

Charges All Partners 90% Rule Capped = 
CALCULATE( SUMX( VALUES( Monthly[Provider] ), [Average charges per partner 90 Capped]),Monthly[Partner Status]="Partner")





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





2 REPLIES 2
Super User
Super User

Re: adding up all values in a previous calculation

@jjkmd I don't know how your tables are set up, why you are doing ALL (monthly[provider] and All(Provider)

 

Looks like you have provider table linked with monthly table.

 

Try following measure to sum up average

 

Charges All Partners 90% Rule Capped = 
CALCULATE( SUMX( VALUES( Monthly[Provider] ), [Average charges per partner 90 Capped]),Monthly[Partner Status]="Partner")





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





jjkmd Frequent Visitor
Frequent Visitor

Re: adding up all values in a previous calculation

Thanks, that worked with one minor tweak

 

Charges all partners capped via forums = CALCULATE( SUMX( VALUES( Monthly[Provider] ), [Average charges per partner 90 Capped]),all(Provider),Monthly[Partner Status]="Partner")
 
That gets me....
Capture.JPG
 
You were right I did not need both ALL (monthly[provider]) and All(Provider); but I do need the All(Provider) in order for each row above to be the 3.4M number.  Interestingly the ALL (monthly[provider]) does not get that.
 
Your formula got me 
Capture1.JPG
 
Still, that got me on the very right track.  it appears the key was to use the SUMX( VALUES( Monthly[Provider] ).  I"m going to have to research what kind of alchemy that "values" does.  ALso why you used Monthly[provider] with "Values".  I think once I understand that some concepts will click into place.
 
Thank you @parry2k 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 263 members 2,960 guests
Please welcome our newest community members: