Remove Row Context from a hierarchy column in Matrix visualization

Question

Hi All,&nbsp;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 -&gt; Brand -&gt; Subbrand. However not all Sub brands are available in all countries.&nbsp;Below is the structure of matrix visual:&nbsp;SalesCompanyABC Sales&nbsp;&nbsp; BrandABC Brand Sales&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sub BrandABC Sub Brand Sales&nbsp;Since specific Sub Brands might be available in certain countries and not all the base calculation is not able to calculate the correct base.&nbsp;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).&nbsp;My Calucaltion:&nbsp;Share =&nbsp;VAR Numerator =SUM(SALES_VALUE)VAR Denominator =CALCULATE(SUM(SALES_VALUE),ALLEXCEPT (TABLENAME,TABLENAME['FILTER COL 1'], --CountryTABLENAME['FILTER COL 2'], --Segments))RETURNDIVIDE(Numerator , Denominator, 0)&nbsp;Help would be appreciated!

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Since you want to keep the selections of filtering, such as slicers. You can change your formula as
&nbsp;
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'] )
                &amp;&amp; TABLENAME['FILTER COL 2'] = MAX ( TABLENAME['FILTER COL 2'] )
        )
    )
RETURN
    DIVIDE ( Numerator, Denominator, 0 )

&nbsp;
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.
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

Forum Discussion

Remove Row Context from a hierarchy column in Matrix visualization

1 Reply

Related content

Avoid blanks on a hierarchy without modifying context

Filter on Visual Row Context

Remove visual filter context

Matrix Visual Hierarchy Level Problem

Slicer and Visualization Hierarchy

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