cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

 

Picture1.png

 

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

 

Thank you in advance.

 

AlBers

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Cumulative percentage of purchases to suppliers

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

12 REPLIES 12
Highlighted
Super User IV
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])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Anonymous
Not applicable

Re: Cumulative percentage of purchases to suppliers

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

Highlighted
Community Support
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,

 

Thank you for your answer.

 

But your formula gives me the following case:

 

Picture1.png

 

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

Highlighted
Community Support
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") ...

 

Picture1.png

 

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
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
Super User III

Re: Cumulative percentage of purchases to suppliers

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Anonymous
Not applicable

Re: Cumulative percentage of purchases to suppliers

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 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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