- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- How to calculate acumulated values without a visua...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

gluizqueiroz

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-01-2019
06:02 AM

**I'm trying to create a ABC Analysis, yes, I already tested the ABC Analysis by DAX Patterns and it is to slow for my use case.**

I have the following table (For example, the table has more values than that):

I created some measures to acumulate % about sales over total sales, following measures:

TotalSales = SUM(Fact_Sales[Value_Sales]) TotalSalesStatic = CALCULATE([TotalSales]; ALL(Fact_Sales)) Percent = DIVIDE([TotalSales]; [TotalSalesStatic]) AcumulatedValue = CALCULATE([TotalSales]; TOPN(RANKX(ALL(Fact_Sales); [TotalSales]); ALL(Fact_Sales[Id_Product]); [TotalSales])) %AcumulatedValue = DIVIDE(Fact_Sales[AcumulatedValue]; [TotalSalesStatic]) Classification = IF([%AcumulatedValue] < 0,7; "A"; IF([%AcumulatedValue] >= 0,7 && [%AcumulatedValue] < 0,9; "B"; IF([%AcumulatedValue] >= 0,9; "C") ) )

When I put this measures on a visual table, I have the following result (For example, the table has more rows than that, including Classification B and C):

As you can see, the column [%AcumulatedValue] is working nice and the Classification too, my problem is when I wanna put a "SUM of %AcumulatedValue" where Classification = A on a card, for example.

In other words, I wanna can extract the values where Classification is A or B or C. I wanna do this to can do the following wonderful measures:

SalesOfClassificationA = CALCULATE(SUM(Fact_Sales[Value_Sales]); FILTER(Fact_Sales; [Classification] = "A") SalesOfClassificationB = CALCULATE(SUM(Fact_Sales[Value_Sales]); FILTER(Fact_Sales; [Classification] = "B") SalesOfClassificationC = CALCULATE(SUM(Fact_Sales[Value_Sales]); FILTER(Fact_Sales; [Classification] = "C")

If I make this measure and put it on a card, I have the following result:

This is not a SUM Sales of Classification A for true, this a total SUM os Sales, not especific Classification A.

I wanna SUM the Sales, SUM the Cost of Produtos, Calculate the Profit and show to user the [%AcumulatedValue] for products where the Classification = "A" or "B" or "C" in a **CARDS, **not in a **VISUAL TABLE**

Is it possible to do with pure measures? I need it totally dynamic.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Greg_Deckler

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-01-2019
06:37 AM

What about this Quick Measure?

https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/m-p/479146

Proud to be a Datanaut!

5 REPLIES 5

Greg_Deckler

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-01-2019
06:37 AM

What about this Quick Measure?

https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/m-p/479146

Proud to be a Datanaut!

gluizqueiroz

Member

Re: How to calculate acumulated values without a visual table using pure measures to ABC Analysis

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-01-2019
07:38 AM

Hey @Greg_Deckler.

Better than I expected!

I'm trying to adapt your quick mesure to my personal case, if I had some problems, can I ask you?

Thanks!

Greg_Deckler

Super User

Re: How to calculate acumulated values without a visual table using pure measures to ABC Analysis

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-01-2019
10:04 AM

Sure.

Proud to be a Datanaut!

gluizqueiroz

Member

Re: How to calculate acumulated values without a visual table using pure measures to ABC Analysis

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-05-2019
08:05 AM

Hey @Greg_Deckler!

Thanks for too much help, your solution works perfectly!

Thanks again! Have a nice day man.

gluizqueiroz

Member

Re: How to calculate acumulated values without a visual table using pure measures to ABC Analysis

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-07-2019
03:23 AM

Hey @Greg_Deckler.

I've been working on the project ABC Analysis and i have a problem on a specific part.

My problem is:

I have 5 slicers on my project, but only 3 slicers should affect the calculate of ABC, how I can "ignore" this slicers?

I tried **ALLEXCEPT**, and it worked, but the project get too slow, without **ALLEXCEPT** (using **ALLSELECTED**) the project goes fast.

mABC Class = VAR __salesTable = ADDCOLUMNS(ALLSELECTED(BI_ABCAnalysis);"__TotalSale";BI_ABCAnalysis[Value_Sale]) VAR __salesTable1 = GROUPBY(__salesTable;BI_ABCAnalysis[Id_Product];"__ProductSales";SUMX(CURRENTGROUP();[__TotalSale])) VAR __salesTable2 = ADDCOLUMNS(__salesTable1;"__CumulatedSales"; SUMX(FILTER(__salesTable1;[__ProductSales]>=EARLIER([__ProductSales]));[__ProductSales])) VAR __totalProductSales = SUMX(__salesTable1;[__ProductSales]) VAR __salesTable3 = ADDCOLUMNS(__salesTable2;"__CumulatedPercentage"; DIVIDE([__CumulatedSales];__totalProductSales;0)) VAR __salesTable4 = ADDCOLUMNS(__salesTable3;"__ABC Class"; SWITCH(TRUE();[__CumulatedPercentage]<=0,7;"A";[__CumulatedPercentage]<=0,9;"B";"C")) VAR __salesTable5 = FILTER(__salesTable4;BI_ABCAnalysis[Id_Product] = MAX(BI_ABCAnalysis[Id_Product])) RETURN MAXX(__salesTable5;[__ABC Class])

I change the first line:

FROM THIS:

VAR __salesTable = ADDCOLUMNS(ALLSELECTED(BI_ABCAnalysis);"__TotalSale";BI_ABCAnalysis[Value_Sale])

TO THIS:

VAR __salesTable = ADDCOLUMNS(ALLEXCEPT(BI_ABCAnalysis; BI_ABCAnalysis[Group]; BI_ABCAnalysis[Subgroup]; BI_ABCAnalysis[Product]);"__TotalSale";BI_ABCAnalysis[Value_Sale])

Currently my project have near 122 thousand rows and in the course of time, my project goes bigger than now.