Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
I need help on concatenatex in the Dax expression. Please find the below Clustered Column chart's tool tip.
Help me on the below,
1. Need Distinct values. The values are repeating ex : P4,P4 in the second bar.
2. Single value should appear on 4th & 5th bar. now its showing P2, P5 on both bars.
Details (Pareto analysis) : The data is simplified, it has Brand, Product, GMV value. Based on GMV value the Share after filteres applied needs to be calculated. The cut-off is 80% to be labelled as Power Product/ not amongst the selections. On labeling the product either as Power/not the Stock status count is required as below but the tool tip is not correct.
Sample PBIX file attached.
https://drive.google.com/file/d/1nHRLqw13pXKVHSr1QI2urk-BWq6D9XF1/view?usp=drivesdk
Regards,
Arjun DN
Solved! Go to Solution.
Welcome,
I just followed the steps on :
https://www.daxpatterns.com/abc-classification-dynamic/
They have a great explanation there. In case you find it difficult to understand I suggest to look at the file I created for you to see how your data relates to the explanation.
I kindly ask you to mark my answer as the solution. A thumbs up for the effort is appreciated. Many thanks!
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Can you help me a bit more on this one?
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi Steve,
The Concatex expression is showing wrong in the tooltip. EX P4,P4 this is a duplicate in the charts Tooltip. I need help on this to get the distinct values.
Please let me know if this is not clear.
Regards,
Arjun DN
This should be what you are looking for:
ConCatX_Prd =
var __sumtbl = SUMMARIZE(Table_Measure;Table_Measure[Product])
return
CONCATENATEX(__sumtbl;[Product];",")
Please mark as solution if it works for you, thumbs up for the effort appreciated.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi Steve,
Thank you very much the issue of duplicates resolved, now I get P4 in the tooltip once.
The chart below shows wrong results, please help me on this.
Expected result is that the Bar B3-No should not appear and the B3-yes should show the count as 2 with 2 colors piles upon for P2,P5.
Hi,
I think you are trying to implement a dynamic ABC pattern:
https://www.daxpatterns.com/abc-classification-dynamic/
I have implemented this for you. Please note a couple of things:
Added a product dimension:
Added the range table:
The relationship:
All the calculations I added have been pre-fixed with a "_"
I changed one value because it was exactly the same as another value:
The result is as expected:
Product 1 and 2 being your power products.
The code:
_RevenuePowerYesNo =
CALCULATE (
[_Revenue];
VALUES ( 'Product'[Product] );
FILTER (
CALCULATETABLE (
ADDCOLUMNS (
ADDCOLUMNS (
VALUES ( 'Product'[Product] );
"OuterValue"; [_Revenue]
);
"CumulatedSalesPercentage"; DIVIDE (
SUMX (
FILTER (
ADDCOLUMNS (
VALUES ( 'Product'[Product] );
"InnerValue"; [_Revenue]
);
[InnerValue] >= [OuterValue]
);
[InnerValue]
);
CALCULATE (
[_Revenue];
VALUES ( 'Product'[Product] )
)
)
);
ALL ( 'Product' )
);
[CumulatedSalesPercentage] > [_MinLowerBoundary]
&& [CumulatedSalesPercentage] <= [_MaxUpperBoundary]
)
)
Link to the Power BI file:
Please mark as solution if it works for you, (couple of) thumbs up for the effort is appreciated.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Does this work for you? Please mark if so.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi Steve,
Thanks for your response. All works fine for me. Appreciate your effort on Data Model and the DAX expression.
Please let me know how to write/understand this DAX may be in a step by step in excel, that helps me the next time.
except for the below when I drill-down to next-level in hierarchy (click the two down-arrow in parallel icon)
Regards,
Arjun DN
Welcome,
I just followed the steps on :
https://www.daxpatterns.com/abc-classification-dynamic/
They have a great explanation there. In case you find it difficult to understand I suggest to look at the file I created for you to see how your data relates to the explanation.
I kindly ask you to mark my answer as the solution. A thumbs up for the effort is appreciated. Many thanks!
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi,
The download link is not working?
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi Steve,
Can you check the link again now? Please let me know if you are ablet to download.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |