cancel
Showing results for
Did you mean:
Highlighted
Anonymous
Not applicable

## Cumulative percentage of purchases to suppliers

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 ?

AlBers

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

## Re: Cumulative percentage of purchases to suppliers

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
12 REPLIES 12
Highlighted
Super User IV

## Re: Cumulative percentage of purchases to suppliers

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

Proud to be a Super User!

Highlighted
Anonymous
Not applicable

## Re: Cumulative percentage of purchases to suppliers

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

Highlighted
Community Support

## Re: Cumulative percentage of purchases to suppliers

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Anonymous
Not applicable

## Re: Cumulative percentage of purchases to suppliers

Hello @v-lili6-msft,

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

Highlighted
Community Support

## Re: Cumulative percentage of purchases to suppliers

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Anonymous
Not applicable

## Re: Cumulative percentage of purchases to suppliers

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 !

Highlighted
Community Support

## Re: Cumulative percentage of purchases to suppliers

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User III

## Re: Cumulative percentage of purchases to suppliers

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Anonymous
Not applicable

## Re: Cumulative percentage of purchases to suppliers

Hello @Ashish_Mathur and @v-lili6-msft,

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

The two actual solutions give a wrong result ...

Best regards

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors