Create Dynamic ABC classification On Full year projection (Using Actual + forecast values)

[Anonymous]

Hi guys, 

Fairly new to Power BI, we are struggling to create a Dynamic ABC classification on a full year projection. 

We have a multiple datasets taken from our SQL database as well as dataset taken from Excel files, for example invoices and forecast

For each dataset we have created multiples measures, and one of which is our Full year projection with is equal to Total YTD + Forecast balance. 
All of this is working perfectly, and we are now trying to create an ABC classification that changes dynamically based on the date (we have set up a Calendar table). 

Were we are stucked is when it comes to the calculation of our cumulative contribution which will allow us then to classify by A/B/C Category. 
We have been trying to use the solution from Dax patterns (https://www.daxpatterns.com/abc-classification/#:~:text=Static%20ABC%20classification%20assigns%20a,...) but with any success, and we think this is because we are pulling the data from multiple tables, and using measures. 

We have been trying all sort of calculations, but can't figure out how to calculate a Cumulative contribution % as shown below. 

Would you get any idea ? 

Thanks for your help

[v-yingjl · Accepted solution]

Hi @Anonymous ,

If you want to calculate the Cumulative value and classify them, you can create these measures:

Total sales = SUM('Table'[Sales])

Cumulative Sales = 
CALCULATE (
    [Total sales],
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date] <= MAX ( 'Calendar'[Date] )
            && 'Table'[Item] IN DISTINCT ( 'Table'[Item] )
    )
)

Category = 
SWITCH (
    TRUE (),
    [Cumulative Sales] < 1000, "Category X",
    [Cumulative Sales] < 10000, "Category Y",
    [Cumulative Sales] < 100000, "Category Z",
    "No correspnding category"
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

I have done bucketing on noncumulative measures. You can refer that here https://youtu.be/CuczXPj0N-k

[amitchandak]

@Anonymous , refer Page 2 in the file attached after signature if that can help