Cumulative percentage of purchases to suppliers

[Anonymous]

Hello,

I want to find the cumulative percentage of the purchases made to suppliers.

I have a measure that calculates the total purchases by supplier :

Product Purchases = SUMX(VALUES('All'[Nom fournisseur]),CALCULATE(SUM('All'[Valeur CHF])))

And I tried to calculate the cumulative percentage :

Cumulative Percentage =

var sales=CALCULATE(SUM('All'[Valeur CHF]),FILTER(ALLSELECTED('All'[Nom fournisseur]),ISONORAFTER('All'[Nom fournisseur],MAX('All'[Nom fournisseur]),DESC)))

var totalsales=CALCULATE(SUM('All'[Valeur CHF]),ALLSELECTED('All'))

return DIVIDE(sales,totalsales)

However, with this way of doing things, the cumulation is done by the alphabetic supplier name and not by the total amount of purchases.

Can you please help me to make the cumulation by the total amount of purchases by supplier ?

Thank you in advance.

AlBers

[amitchandak]

You can cumulative by the supplier or by time. Not very clear what you want

for time you could have done like Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))

[Anonymous]

Thank you for your answer @amitchandak  !

I want to cumulate by the column (measure) Product Purchases.

But since it's a measure, I cannot use the MAX() function as you say...

[v-lili6-msft]

hi  @Anonymous 

Just adjust the formula as below:

Cumulative Percentage = 
var sales=CALCULATE(SUM('All'[Valeur CHF]),FILTER(ALLSELECTED('All'),'All'[Valeur CHF]<=SUM('All'[Valeur CHF])))
var totalsales=CALCULATE(SUM('All'[Valeur CHF]),ALLSELECTED('All'))
return
DIVIDE(sales,totalsales)

Regards,

Lin

[Anonymous]

Hello @v-lili6-msft,

Thank you for your answer.

But your formula gives me the following case:

It seems like the cumulation is made in the wrong direction and that the percentage is wrongly calculated ...

[v-lili6-msft]

hi  @Anonymous 

If so, just adjust the formula as below:

Cumulative Percentage = 
var sales=CALCULATE(SUM('All'[Valeur CHF]),FILTER(ALLSELECTED('All'),'All'[Valeur CHF]>=SUM('All'[Valeur CHF])))
var totalsales=CALCULATE(SUM('All'[Valeur CHF]),ALLSELECTED('All'))
return
DIVIDE(sales,totalsales)

Regards,

Lin

[Anonymous]

Hello @v-lili6-msft,

It still doesn't work ("Cumulative Percentage 2") ...

Maybe we have to use a measure like this one :

Product Purchases = SUMX(VALUES('All'[Nom fournisseur]),CALCULATE(SUM('All'[Valeur CHF])))

Used to calculate the sum of purchases values by supplier.

Thanks a lot ! Best regards !

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Hello @Ashish_Mathur and @v-lili6-msft,

Thank you for your answers !

Here is the sample pbix file : https://we.tl/t-lmOY1iuUBz

The two actual solutions give a wrong result ...

Best regards 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Nice !!!

Thank you so much @Ashish_Mathur !!!

Have a nice afternoon, best regards.

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[v-lili6-msft]

hi  @Anonymous 

If possible, could you please share your sample pbix file for us have a test? that will be a great help.

Regards,

Lin