Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
Hi @George1973
Please download and check sample file with the solution https://www.dropbox.com/t/pBW046fcBJHu5mlN
This is how to do this in a professional and robust manner:
@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]..
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!!!
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:
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
or
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
I'll look into it and get back to you
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:
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.
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
10 | |
6 |