cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
denpries
Resolver I
Resolver I

Count of material types responsible for top 80% of sales

Dear community.

 

I looked at many different solutions but dont have one yet; i dont know how to tackle this.

The complex part is that the value should be responsive to all present filters.

 

I can do such thing in a calculated table, added columns etc. But then it does no longer respond to my filters as of course this number is different for different regions. So therefore i need to look at an other approach...

 

I had quite some starting points, also this one: https://community.powerbi.com/t5/Desktop/Top-customers-who-are-contributing-to-the-80-of-the-total-a... but although this could show me rank of the materials i failed to convert this example into a single-value measure that shows the amount of materials involved to end up at the 20% sales.

 

 

In the end i want to know:

- How many different (top selling) materials are involved in the first 80% of the sales?

- What is the sum of the expenses on those materials?

 

My approach so far: basically i have (for this example) a table with e.g. four columns:

 

customer | material | sales | expenses

 

in a dax measure i make a summarized virtual table to get rid of the customer and get the ranking on sales in that virtual table as well.

 

VAR vtable =
SUMMARIZE(
salestable;
sales[materials]; "Ranking";RANKX(ALLSELECTED(salestable);[sales];;DESC);
"Sales";[Sales];"Expenses";[Expenses])

 

Next, i thought i could do a calculate() to sumup for every rank all sales higher or equal to that rank number.  [could not realize this in virtual table]

THen, i figured i should compare the cumulative sales with 80% of sales, and count any cumulative sales >= 80% of the sales, and display the total counted number in the end value. Equally, sumup all costs for when **bleep**.sales >= 80%. 

 

However, i fail to do so yet. Is there a better approach for this?

2 ACCEPTED SOLUTIONS

Got to love self service


Similar to above, the first vtable is created. 
Next, vtable2 is created, with a new column v2cumsales to hold the cumulative sales. 

This is done using

 

SUMX(FILTER(vtable;EARLIER([vRanking])>=[vRanking]);[vSales])

Which basically sums sales over all materials where the ranking BEFORE the virtual row context came in place (hence the earlier) is equal or higher than the current ranking in the virtual row context of virtual table 1. 

 

 

test2 = 
VAR totalsales = [Sales]
VAR vtable = 
        SUMMARIZE(
        salestable;
        salestable[materialid]; 
        "vRanking";RANKX(ALLSELECTED(salestable);[Sales];;DESC);
        "vSales";[Sales])
VAR vtable2 = ADDCOLUMNS(vtable;
        "v2CumSales";SUMX(FILTER(vtable;EARLIER([vRanking])>=[vRanking]);[vSales]))
    RETURN 
COUNTX(
    FILTER(vtable2;[v2CumSales]<=totalsales*0,2);
    [vSales]
)

 

The countx part counts all instances where the cumulative sales is lower than or equal to 20% of the total sales. So therefore retrieving the set of highest selling materials that make up for 20% of the sales.  If i want to have exact sales of this set of materials, i replace the COUNTX with SUMX. If you also want the exact costs, you need to add costs to the vtable.

 

For now, i consider this solved, and perhaps it can also function as a tutorial / show and tell?

 

 

open question to me: i dont understand why it does not work if i replace the measure [Sales] in the vtable part by sum(Salescolumn). Perhaps it refers to the wrong context then?

View solution in original post

@denpries,

 

Every measure has an implicit CALCULATE that you can’t see, so measures always trigger context transition from within a row context.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
denpries
Resolver I
Resolver I

I do feel i am on to something here though...

 

in the first virtual table i summarize on materialid over the salestable, and include the ranking plus the sales as well. Sales i call vSales in this case. 

 

As i cannot directly refer to the freshly made columns vSales and vRanking, i need to add a column with addcolumns(). 
In the argument of addcolumns i add a new column v2test, which (for now) is completely filled with the top sales of the material that is ranked 1. 

 

QUESTION: how do i get this ' v2test'  column to fill with the cumulative sales of rank 1 until (and including) the current row rank? Normally i would go for calculate() but i doubt this is the right approach here.

 

Dont mind the part below the RETURN yet. It is supposed to do a COUNTX on the new column created above.

 

 

test2 = 
VAR vtable = 
        SUMMARIZE(
        salestable;
        salestable[materialid]; 
        "vRanking";RANKX(ALLSELECTED(salestable);[Sales];;DESC);
        "vSales";[Sales])
VAR vtable2 = ADDCOLUMNS(vtable;
        "v2Test";SUMX(FILTER(vtable;[vRanking]=1);[vSales]))
    RETURN 
AVERAGEX(
    vtable2;
        //Increase by multiple
        [test]
)

 

Got to love self service


Similar to above, the first vtable is created. 
Next, vtable2 is created, with a new column v2cumsales to hold the cumulative sales. 

This is done using

 

SUMX(FILTER(vtable;EARLIER([vRanking])>=[vRanking]);[vSales])

Which basically sums sales over all materials where the ranking BEFORE the virtual row context came in place (hence the earlier) is equal or higher than the current ranking in the virtual row context of virtual table 1. 

 

 

test2 = 
VAR totalsales = [Sales]
VAR vtable = 
        SUMMARIZE(
        salestable;
        salestable[materialid]; 
        "vRanking";RANKX(ALLSELECTED(salestable);[Sales];;DESC);
        "vSales";[Sales])
VAR vtable2 = ADDCOLUMNS(vtable;
        "v2CumSales";SUMX(FILTER(vtable;EARLIER([vRanking])>=[vRanking]);[vSales]))
    RETURN 
COUNTX(
    FILTER(vtable2;[v2CumSales]<=totalsales*0,2);
    [vSales]
)

 

The countx part counts all instances where the cumulative sales is lower than or equal to 20% of the total sales. So therefore retrieving the set of highest selling materials that make up for 20% of the sales.  If i want to have exact sales of this set of materials, i replace the COUNTX with SUMX. If you also want the exact costs, you need to add costs to the vtable.

 

For now, i consider this solved, and perhaps it can also function as a tutorial / show and tell?

 

 

open question to me: i dont understand why it does not work if i replace the measure [Sales] in the vtable part by sum(Salescolumn). Perhaps it refers to the wrong context then?

View solution in original post

Hi,

 

I've tried the code you have below on PowerPivot and I am getting (blank). Seems v2CumSales within vTable2 is populated with the total total, instead of the running total.

 

In my working example (below), I can validate that vTable works as expected, but my vTable2 doesn't work at all. It seems that if I replace all instances of the VAR by the actual code for said VARs it works. Performance-wise, it is terrible though. Any idea why?

 

MaterialID

(data)

Sales (data)Rank (vtable)

v2CumSales

(vtable2)

a105150
b204150
c303150
d402150
e501150

@denpries,

 

Every measure has an implicit CALCULATE that you can’t see, so measures always trigger context transition from within a row context.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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