Help With Mekko Chart - Dynamically summarizing data

[Biru]

Hello Everyone,

I am trying to build a Mekko chart as below.Sample data sheet is attached here Mekko Data Sheet 

Now,the only thing constant in the data is Market share for Comp1 to Comp 6 for each product.There could be specific competitors for specific product and there is other competitor category for each product.

Requirement - When we are trying to summarize the market share,we want competitors other than Comp 1 to Comp 6 to be added to the Other Competitors market share in the dashboard.In other words dashboards should have only Comp 1 to Comp 6 and other competitors.Where if we have any additional competitor other than Comp 1 to Comp 6,thier market share will be added with "Other Competitors" and displayed in the dashboard.

Regards

Biru

[Biru · Accepted solution]

So i was able to work this out with little research.Sharing if it helps someone else.There could be a better way to do it though 🙂

Created a new column.

Mekko_Competitors = IF([Competitor]="Comp1","Comp1",IF([Competitor]="Comp2","Comp2",IF([Competitor]="Comp3","Comp3",IF([Competitor]="Comp4","Comp4",IF([Competitor]="Comp5","Comp5",IF([Competitor]="Comp6","Comp6","Others")))))

And now i can use this new measure in Mekko which will summarize competitors other than 1-6 in mekko graph.

Regards

Biru

[Biru · Accepted solution]

So i was able to work this out with little research.Sharing if it helps someone else.There could be a better way to do it though 🙂

Created a new column.

Mekko_Competitors = IF([Competitor]="Comp1","Comp1",IF([Competitor]="Comp2","Comp2",IF([Competitor]="Comp3","Comp3",IF([Competitor]="Comp4","Comp4",IF([Competitor]="Comp5","Comp5",IF([Competitor]="Comp6","Comp6","Others")))))

And now i can use this new measure in Mekko which will summarize competitors other than 1-6 in mekko graph.

Regards

Biru

[Icey · Accepted solution]

Hi @Biru ,

Glad to here that. Please accept your reply as the solution. Your contribution is highly appreciated.

In addition, you can also create the column like so:

Competitors 2 = 
IF (
    [Competitors] IN { "Comp1", "Comp2", "Comp3", "Comp4", "Comp5", "Comp6" },
    [Competitors],
    "Other Competitors"
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Biru]

Is there any IF,Else statement in Power BI which i can use.Lets say i can create a new measure where the value of the measure will be as it is if the competitor is Comp 1 to Comp 6 otherwise put the value as "Other Competitor" in place of comp7,Comp8 etc..

IF NOT ( 'Sheet1'[Competitor]) IN { "Comp1", "Comp2", "Comp3", "Comp4", "Comp5", "Comp6" }

Move "Other Competitors" TO New_Measure

Else

Move ( 'Sheet1'[Competitor]) TO New_Measure

End-if