topic Re: Cumulative Spend measure returning incorrect results in Spend Range measure in DAX Commands and Tips

Cumulative Spend measure returning incorrect results in Spend Range measure

Anonymous — Tue, 25 Apr 2023 08:25:35 GMT

I have a table in Power BI called 'SC data all' with columns 'Supplier Name' and 'Spend (EUR)'. I created a Cumulative Spend measure using the following formula:

Cumulative Spend =

VAR TotalSpend = CALCULATE(SUM('SC data all'[Spend (EUR)]),ALLSELECTED('SC data all'))

VAR CurrentSpend = SUM('SC data all'[Spend (EUR)])

VAR SummarizedTable =

SUMMARIZE(

ALLSELECTED('SC data all'),

'SC data all'[Supplier Name],

"Spend",SUM('SC data all'[Spend (EUR)])

)

VAR CumulativeSum =

SUMX(

FILTER(SummarizedTable,[Spend] >= CurrentSpend),

[Spend]

)

RETURN

DIVIDE(CumulativeSum,TotalSpend)

This formula is working fine, no issues here.

But now I would like tot add a range like:

Cumulative Spend <0.8 "Top 80%"

Cumulative Spend 0.8<>0.95 "80 to 95%"

Cumulative Spend >0.95 "Bottom 5%"

I use the following formula:

Vendor Pareto =

VAR TotalSpend = CALCULATE(SUM('SC data all'[Spend (EUR)]),ALLSELECTED('SC data all'))

VAR CurrentSpend = SUM('SC data all'[Spend (EUR)])

VAR SummarizedTable =

SUMMARIZE(

ALLSELECTED('SC data all'),

'SC data all'[Supplier Name],

"Spend",SUM('SC data all'[Spend (EUR)])

)

VAR CumulativeSum =

SUMX(

FILTER(SummarizedTable,[Spend] >= CurrentSpend),

[Spend]

)

RETURN

SWITCH(TRUE(),

DIVIDE(CumulativeSum, TotalSpend) > 0.95, "Bottom 5%",

DIVIDE(CumulativeSum, TotalSpend) < 0.1, "Top 80%",

AND(DIVIDE(CumulativeSum, TotalSpend) >= 0.8, DIVIDE(CumulativeSum, TotalSpend) <= 0.95), "80 to 95%",

BLANK()

)

And I can't get this formula to work. All my results are "Top 80%"

It should not be this hard because the measure Cumulative Spend is working just fine.

Anybody knows what I am doing wrong? Much appreciated! 🙂

Re: Cumulative Spend measure returning incorrect results in Spend Range measure

Wilson_ — Mon, 24 Apr 2023 14:21:46 GMT

Hello shanti,

Your Cumulative Spend and Vendor Pareto measures are not exactly the same. They are using different CALCULATE parameters in the TotalSpend variable: Cumulative Spend is using "ALL('SC data all'[Supplier Name])", whereas Vendor Pareto is using "ALLSELECTED('SC data all')".

Hard to know whether this is the only issue (or an issue at all), since you haven't shared any data.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

Re: Cumulative Spend measure returning incorrect results in Spend Range measure

Anonymous — Mon, 24 Apr 2023 14:31:12 GMT

Hello Wilson,

Thanks for your quick response. You are right, I posted the wrong Cumulative Spend formula. Both are using the ALLSELECTED. I will edit my original post.

About sharing data, what would be the best way to share a sample of my data since it is a huge report/dataset. Create a new report with samples and post the .pbix file?

Re: Cumulative Spend measure returning incorrect results in Spend Range measure

Wilson_ — Mon, 24 Apr 2023 16:34:36 GMT

Shanti,

Yes, that would probably be the best way to share.

Re: Cumulative Spend measure returning incorrect results in Spend Range measure

Anonymous — Tue, 25 Apr 2023 08:26:47 GMT

I added a .pbix file
Link to .pbix sample report

Re: Cumulative Spend measure returning incorrect results in Spend Range measure

Wilson_ — Wed, 26 Apr 2023 12:45:25 GMT

Hello shanti,

Okay, well I should've seen this sooner but your measure is showing "CumulativeSpend < 0.1, "Top 80%" in the measure when it should be "CumulativeSpend < 0.8, "Top 80%" presumably. This might just be a typo? 😄

Re: Cumulative Spend measure returning incorrect results in Spend Range measure

Anonymous — Thu, 27 Apr 2023 08:39:06 GMT

Hi Wilson,

That would have been an embarassing but a quick solution 😂

Unfortunately it doesn't seem to matter. I was juggling around with the numbers to see if it made any difference on the outcome and forgot to retun them to the original settings.

But the formula is still not giving me the outcome I expect.

Re: Cumulative Spend measure returning incorrect results in Spend Range measure

Anonymous — Thu, 27 Apr 2023 10:14:37 GMT

I figured it out and it is equally embarrasing...

I added the formula as a column instead of a measure.

Now I made the changes is works like a charm.

Thanks for your time & effort in trying to help me out! 😀

Re: Cumulative Spend measure returning incorrect results in Spend Range measure

Wilson_ — Thu, 27 Apr 2023 12:16:17 GMT

haha I love it when my problem was just user error. Those are usually the easiest solutions! 😄

They were measures in your sample pbix though so I definitely never would've caught that hah.