topic Re: Need help on tool tip - Pareto Analyis in DAX Commands and Tips

Need help on tool tip - Pareto Analyis

Anonymous — Mon, 01 Jun 2020 07:16:21 GMT

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

Re: Need help on tool tip - Pareto Analyis

stevedep — Tue, 02 Jun 2020 09:32:43 GMT

Hi,

The download link is not working?

Re: Need help on tool tip - Pareto Analyis

Anonymous — Wed, 03 Jun 2020 05:12:00 GMT

Hi Steve,

Can you check the link again now? Please let me know if you are ablet to download.

Re: Need help on tool tip - Pareto Analyis

stevedep — Wed, 03 Jun 2020 07:21:20 GMT

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

Re: Need help on tool tip - Pareto Analyis

Anonymous — Mon, 08 Jun 2020 14:57:11 GMT

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

Re: Need help on tool tip - Pareto Analyis

stevedep — Mon, 08 Jun 2020 15:34:56 GMT

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.

Re: Need help on tool tip - Pareto Analyis

Anonymous — Tue, 09 Jun 2020 05:54:14 GMT

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.

Re: Need help on tool tip - Pareto Analyis

stevedep — Tue, 09 Jun 2020 07:55:34 GMT

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.

Re: Need help on tool tip - Pareto Analyis

stevedep — Fri, 12 Jun 2020 07:20:56 GMT

Does this work for you? Please mark if so.

Re: Need help on tool tip - Pareto Analyis

Anonymous — Mon, 15 Jun 2020 12:43:10 GMT

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

Re: Need help on tool tip - Pareto Analyis

stevedep — Mon, 15 Jun 2020 12:55:00 GMT

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!