Need help on tool tip - Pareto Analyis

[Anonymous]

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

[stevedep]

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

[Anonymous]

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

[stevedep]

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. 

[Anonymous]

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.

[stevedep]

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. 

[stevedep]

Does this work for you? Please mark if so.

[Anonymous]

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

[stevedep · Accepted 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!

[stevedep]

Hi,

The download link is not working?

[Anonymous]

Hi Steve,

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