Dynamic Filtering for Mekkos

[Anonymous]

Hi, 

I am new to the PowerBi forum and hope somebody is able to help solve something i truly believe to be possible! (we love Mekkos)

My question is around dynamic filtering with the PBI Mekko (or maybe i need to construct my own, who knows). 

A simple example of the format my data is in (not my actual dataset).

Category  Manufacturer  $ Value

A               Microsoft        1,000

B                Apple             2,000

C                Dell                500

B                HP                  350

C                Apple             500

B                Microsoft       500      

A                Lenovo          750  

A                Samsung       300      

A is $2,050

B is $2,850

C is $1,000

Now if i build a Mekko all the manufacturers will show up and be different coloured to show their respective share of A, B and C. However, what i am after is a way to select specific manufacturers and "categorize" the ones not selected as "other" so that way their data is still included in the calculation of the total category share (the goal is to enture that the category's A, B and C still hold the size as totalled above, but the filtering allows me to change the visual and recalculates the "other" share based on selections. Example:

Pick Apple, Microsoft and Lenovo. 

A has $1,750 for Micro and Lenovo (with $300 as "other")

B has $2,500 for Apple and Micro (with $350 as "other")

C has $500 for Apple (with $500 as "other")

This could then be recalculated if i were to pick Dell, HP and Apple - or some other combination. 

The utility of the above does not appear significant with such a small dataset, but i am dealing with >10,000 manufacturers in my actual data set. Any help on the DAX or anything required to build this would be much appreciated.

Thanks, 

Morgan.

[ImkeF · Accepted solution]

Hi @Anonymous ,
if you create another Dimension table for the manufacturers with an additional "Others" row, your measure could look like so:

Mekko = 
VAR __SelectedProductsValue =
    SUMX ( ALLSELECTED ( DimManufacturers_WithOthers[Manufacturer] ), [SumValue] )
VAR __AllManufacturersValue =
    CALCULATE ( [SumValue], REMOVEFILTERS(  DimManufacturers ) )
VAR __Result =
    SUMX (
        VALUES ( DimManufacturers_WithOthers[Manufacturer] ),
        IF (
            CALCULATE ( MAX ( DimManufacturers_WithOthers[Manufacturer] ) ) = "Others",
            __AllManufacturersValue - __SelectedProductsValue,
            [SumValue]
        )
    )
RETURN
    __Result

Please check out the file enclosed with details on how to connect the tables and which dimension fields to use where: 

... actually, if you just need it for the Mekko chart and don't need the totals to add up correctly, you can skip the SUM like so:

Mekko = 
VAR __SelectedProductsValue =
    SUMX ( ALLSELECTED ( DimManufacturers_WithOthers[Manufacturer] ), [SumValue] )
VAR __AllManufacturersValue =
    CALCULATE ( [SumValue], REMOVEFILTERS(  DimManufacturers ) )
VAR __Result =

        IF (
            CALCULATE ( MAX ( DimManufacturers_WithOthers[Manufacturer] ) ) = "Others",
            __AllManufacturersValue - __SelectedProductsValue,
            [SumValue]
        )

RETURN
    __Result

[ImkeF · Accepted solution]

Hi @Anonymous ,
this should work for all charts. But you might want to try it out.

[ImkeF · Accepted solution]

Hi @Anonymous ,
if you create another Dimension table for the manufacturers with an additional "Others" row, your measure could look like so:

Mekko = 
VAR __SelectedProductsValue =
    SUMX ( ALLSELECTED ( DimManufacturers_WithOthers[Manufacturer] ), [SumValue] )
VAR __AllManufacturersValue =
    CALCULATE ( [SumValue], REMOVEFILTERS(  DimManufacturers ) )
VAR __Result =
    SUMX (
        VALUES ( DimManufacturers_WithOthers[Manufacturer] ),
        IF (
            CALCULATE ( MAX ( DimManufacturers_WithOthers[Manufacturer] ) ) = "Others",
            __AllManufacturersValue - __SelectedProductsValue,
            [SumValue]
        )
    )
RETURN
    __Result

Please check out the file enclosed with details on how to connect the tables and which dimension fields to use where: 

... actually, if you just need it for the Mekko chart and don't need the totals to add up correctly, you can skip the SUM like so:

Mekko = 
VAR __SelectedProductsValue =
    SUMX ( ALLSELECTED ( DimManufacturers_WithOthers[Manufacturer] ), [SumValue] )
VAR __AllManufacturersValue =
    CALCULATE ( [SumValue], REMOVEFILTERS(  DimManufacturers ) )
VAR __Result =

        IF (
            CALCULATE ( MAX ( DimManufacturers_WithOthers[Manufacturer] ) ) = "Others",
            __AllManufacturersValue - __SelectedProductsValue,
            [SumValue]
        )

RETURN
    __Result

[Anonymous]

Hi ImkeF, 

Thank you for the quick response. 

I opened your solution in the PBI and noticed that you are using the "Treemap" rather than a a Marimekko chart (i am using "Mekko Chart 3.2.1").

Will this solution you have provided work across Treemap to my selected chart or will another solution be required? 

Thanks, 

Morgan.