topic Re: Convert Dinamic Measure to Column for its usage as a category in DAX Commands and Tips

Convert Dinamic Measure to Column for its usage as a category

George1973 — Wed, 06 Apr 2022 21:37:17 GMT

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

Re: Convert Dinamic Measure to Column for its usage as a category

tamerj1 — Thu, 07 Apr 2022 03:30:59 GMT

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.

Re: Convert Dinamic Measure to Column for its usage as a category

George1973 — Thu, 07 Apr 2022 04:40:32 GMT

Dear tamim ,

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

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

Re: Convert Dinamic Measure to Column for its usage as a category

tamerj1 — Thu, 07 Apr 2022 05:03:19 GMT

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

Re: Convert Dinamic Measure to Column for its usage as a category

George1973 — Thu, 07 Apr 2022 07:13:35 GMT

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:

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

Re: Convert Dinamic Measure to Column for its usage as a category

tamerj1 — Thu, 07 Apr 2022 08:35:44 GMT

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

Re: Convert Dinamic Measure to Column for its usage as a category

daXtreme — Thu, 07 Apr 2022 09:13:10 GMT

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

ABC classification – DAX Patterns

Re: Convert Dinamic Measure to Column for its usage as a category

George1973 — Thu, 07 Apr 2022 09:52:37 GMT

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

Re: Convert Dinamic Measure to Column for its usage as a category

tamerj1 — Thu, 07 Apr 2022 10:55:39 GMT

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 😅

Re: Convert Dinamic Measure to Column for its usage as a category

George1973 — Thu, 07 Apr 2022 14:33:44 GMT

:))
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:

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;

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:

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

(As you can see the tabe names are different)

2. Charts are ok

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 🙂

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

Re: Convert Dinamic Measure to Column for its usage as a category

tamerj1 — Thu, 07 Apr 2022 15:40:55 GMT

I'll look into it and get back to you