Cumulative Spend measure returning incorrect results in Spend Range measure

[Anonymous]

Link to .pbix sample data file 

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! 🙂

[Anonymous]

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

[Wilson_ · Accepted solution]

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? 😄

----------------------------------
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?)

[Anonymous]

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.

[Anonymous]

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! 😀

[Wilson_]

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.

[Wilson_]

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?)

[Anonymous]

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?

[Wilson_]

Shanti,

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