Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Remove Row Context from a hierarchy column in Matrix visualization

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
CompanyABC Sales
   BrandABC Brand Sales
      Sub BrandABC 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!
1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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.           

View solution in original post

1 REPLY 1
v-stephen-msft
Community Support
Community Support

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.           

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.