Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
George1973
Helper V
Helper V

Convert Dinamic Measure to Column for its usage as a category

Hi,

I'm making product ABC analysis for pre-selected single month through slicer and it works. Here is the measure:

ABCD on Profit = 
VAR summary =
    ADDCOLUMNS ( ALLSELECTED( C_GOODS), "cSales", [1st 12M Profitability] )

 VAR ProfitRanks=RANKX(ALLSELECTED(C_GOODS),[1st 12M Profitability])

VAR RunningTotal_Prod_Sales=SUMX (
        TOPN ( ProfitRanks, summary, [cSales], DESC ),
        [cSales]
    )
VAR GrandTotal_for_TotalSales=SUMX ( summary, [cSales] )
VAR RunnigTotal_ProdSales_Shares =
    DIVIDE ( RunningTotal_Prod_Sales, GrandTotal_for_TotalSales, 0 )
RETURN
    SWITCH (
        TRUE (),
        RunnigTotal_ProdSales_Shares <= 0.8, "A",
        RunnigTotal_ProdSales_Shares > 0.8
            && RunnigTotal_ProdSales_Shares <= 0.95, "B",
        RunnigTotal_ProdSales_Shares > 0.95
            && RunnigTotal_ProdSales_Shares <= 0.99, "C",
        RunnigTotal_ProdSales_Shares > 0.99
            && RunnigTotal_ProdSales_Shares <= 1, "D",
        "X"
    )

 

No, I would like to use the results A,B,C,D.. as a category for visualts to count products, values etc..


When I copy-paste this measure into "Calculated Column" ut does not work, showing only "A"..
Also, as you can undestand, for each month the ABCD results for the same product list could be different.

Please help in solving this issue.

Thanks in advance,

George

 

1 ACCEPTED SOLUTION

Hi @George1973 
Please download and check sample file with the solution https://www.dropbox.com/t/pBW046fcBJHu5mlN

View solution in original post

10 REPLIES 10
daXtreme
Solution Sage
Solution Sage

This is how to do this in a professional and robust manner:

ABC classification – DAX Patterns

George1973
Helper V
Helper V

@tamerj1 ,

 

I have created a samble BI data Base, but I can not attach it to the reply. I do not know why.

Here is the matrix:

George1973_0-1649315251354.png

I have shared it through the drop-box:

https://www.dropbox.com/s/02dlo3k8ouzsgwd/Sample%20DB.pbix?dl=0

 

So, once again: The idea is to make ABCD measure a functional "Column-Category" data, which can be used for the other vizuals as a category for X/Y axis.. or whatsoever..

Once again, your logic is very interesting: 

Filter Table =
SELECTCOLUMNS ( { "A", "B", "C", "D" }, "Class", [Value] )

But I do not undesrtand which measure/parameter should I insert in [VALUE]..

Hi @George1973 
Please download and check sample file with the solution https://www.dropbox.com/t/pBW046fcBJHu5mlN

Thanks a lot! Checked and seems that it's a right solution!!!

@George1973 

Still few things need to be done to create the required chart. I can help if you need any further help with DAX code. However, am not the best guy to relay on when comes to charts 😅

:))
Well, well, well - I have crossed checked the formulas. It works in general, but there are some "strange" issues happen, like:
1. The "filter tables" errors:

George1973_0-1649340938767.png

This was the first table I have created according to your suggestions. It was error free.. Now it gives me this.

After that I crated the second table for other category;

George1973_1-1649341033570.png

And it was and is error free.

But, when I try to create another category tabe:

ABC Analysis Filter = 
SELECTCOLUMNS (
     {
    "A-A-A",
    "A-A-B",
    "A-A-C",
    "A-B-A",
    "A-B-B",
    "A-B-C",
    "A-C-A",
    "A-C-B",
    "A-C-C",
    "B-A-A",
    "B-A-B",
    "B-A-C",
    "B-B-A",
    "B-B-B",
    "B-B-C",
    "B-C-A",
    "B-C-B",
    "B-C-C",
    "C-A-A",
    "C-A-B",
    "C-A-C",
    "C-B-A",
    "C-B-B",
    "C-B-C",
    "C-C-A",
    "C-C-B",
    "C-C-C"
     }, "ClassCat", [Value] )

 

I'm am told that: 

George1973_2-1649341174649.png

Even, when I'm trying to use the exactly the same code, which I used for the table #1, just for check:

George1973_3-1649341241819.png

(As you can see the tabe names are different)


2. Charts are ok

George1973_4-1649341347949.png

or

George1973_5-1649341375030.png

But the data itself is calculated wrongly somehow.

3. Errors in Data
https://www.dropbox.com/s/00t5781kkruc4rv/Sample%20Data%20-%20hits.xlsx?dl=0
Here is the Excel Data sheet, where I have extracted the odiginal data. 

In visuals, it says that I have, for example "A" group with 80 records, but in the reality (In excel, it's about 154). For "B" - we have 112, but in excel there is - 239.. and so on 🙂

George1973_6-1649341925701.png


I have tried hard to find out the reasons, but failed.
Please help.. I think there is a tiny thing we're missing.. to get it done fully











 

I'll look into it and get back to you

tamerj1
Super User
Super User

Hi @George1973 
Creating a calculated column requires different code and wont be as dynamic as you would expect. Alternatively you can create a singular column disconnected filter table that contains A, B, C and D and use this column as a slicer or in a martix rows/columns. Example

Filter Table =
SELECTCOLUMNS ( { "A", "B", "C", "D" }, "Class", [Value] )

Then you can use SELECTEDVALUE to compare the selection with the outcome of the measure in the current filter context.
Please provide a sample file and details of the expected results in order to asset you further.

Dear @tamim ,

Thanks for the respond.
Suppose I want to get this result:

George1973_0-1649306404138.png

I can not provide the data itself, because itrs too large 🙂





@George1973 
You may please prepare sample data of only the relevant calculation. You can use dummy data to avoid sharing sensitive information.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.