Remove Row Context from a hierarchy column in Matrix visualization

[Anonymous]

Hi All,

I am working on an use case wherein I am looking at multiple country data and analysing share values in a Matrix visualization. In rows of the matrix visualization I have hierarchy defined as Company -> Brand -> Subbrand. However not all Sub brands are available in all countries.

Below is the structure of matrix visual:

	Sales
Company	ABC Sales
Brand	ABC Brand Sales
Sub Brand	ABC Sub Brand Sales

Since specific Sub Brands might be available in certain countries and not all the base calculation is not able to calculate the correct base.

Example if Sub Brand A is present in lets say India but not in Australia, so the base as in denominator would be country specific which I do not want base, should be on the basis of filter selection (Like whatever countries are selected irrespective of sub brand whether it exists or not in that set of countries).

My Calucaltion:

Share = 

VAR Numerator =

SUM(SALES_VALUE)

VAR Denominator =

CALCULATE(

SUM(SALES_VALUE),

ALLEXCEPT (

TABLENAME,

TABLENAME['FILTER COL 1'], --Country

TABLENAME['FILTER COL 2'], --Segments

)

)

RETURN

DIVIDE(Numerator , Denominator, 0)

Help would be appreciated!

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Since you want to keep the selections of filtering, such as slicers. You can change your formula as

Share =
VAR Numerator =
    SUM ( 'TABLENAME'[SALES_VALUE] )
VAR Denominator =
    CALCULATE (
        SUM ( 'TABLENAME'[SALES_VALUE] ),
        FILTER (
            ALLSELECTED ( TABLENAME ),
            TABLENAME['FILTER COL 1'] = MAX ( TABLENAME['FILTER COL 1'] )
                && TABLENAME['FILTER COL 2'] = MAX ( TABLENAME['FILTER COL 2'] )
        )
    )
RETURN
    DIVIDE ( Numerator, Denominator, 0 )

ALLSELECTED function (DAX) - DAX | Microsoft Learn

The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

Best Regards,

Stephen Tao

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