Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Matrix Visual Total Only at First Row

Hi Friends,

 

Currently i have created a Matrxi visual using the 3 dimension columns and 1 fact column and the result is below.

I am faicng unique situation where i need to show the total only at the first row of the category level. Something like the below.

Note: I dont want to use row totals.
Please suggest me some ideas.
@amitchandak @Ritaf1983 @Greg_Deckler @ChandeepChhabra @GilbertQ @dbeavon3 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    Measure = 
VAR _category =
    SELECTEDVALUE ( 'Table'[Product Category] )
VAR _subcategory =
    SELECTEDVALUE ( 'Table'[Product Sub Category] )
VAR _product =
    SELECTEDVALUE ( 'Table'[Product Name] )
VAR _minsubcat =
    CALCULATE (
        MIN ( 'Table'[Product Sub Category] ),
        ALLEXCEPT ( 'Table', 'Table'[Product Category] )
    )
VAR _minpro =
    CALCULATE (
        MIN ( 'Table'[Product Name] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Product Category] = _category
                && 'Table'[Product Sub Category] = _minsubcat
        )
    )
RETURN
    IF (
        _subcategory = _minsubcat
            && _product = _minpro,
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            ALLEXCEPT ( 'Table', 'Table'[Product Category] )
        )
    )

    Best Regards

3 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Icon for Super User rankSuper User
    1 year ago

    hello Anonymous ,

     

    yes try the following but fill in with your field and table names

    Sales = 
IF (
    ISINSCOPE('Table'[Product Name]),
    BLANK(),  -- For Product Names, show nothing
    IF (
        ISINSCOPE('Table'[Product Sub Category]),
        BLANK(),  -- For Sub Categories, show nothing
            [TotalSales]
        )  -- Show total at Product Category level
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Idrissshatila,

      This wont work becasue the moment first condition satisfies 

      IF (
      ISINSCOPE('Table'[Product Name]),
      BLANK(),  

      It wont go to the next set of IF conditions.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    Measure = 
VAR _category =
    SELECTEDVALUE ( 'Table'[Product Category] )
VAR _subcategory =
    SELECTEDVALUE ( 'Table'[Product Sub Category] )
VAR _product =
    SELECTEDVALUE ( 'Table'[Product Name] )
VAR _minsubcat =
    CALCULATE (
        MIN ( 'Table'[Product Sub Category] ),
        ALLEXCEPT ( 'Table', 'Table'[Product Category] )
    )
VAR _minpro =
    CALCULATE (
        MIN ( 'Table'[Product Name] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Product Category] = _category
                && 'Table'[Product Sub Category] = _minsubcat
        )
    )
RETURN
    IF (
        _subcategory = _minsubcat
            && _product = _minpro,
        CALCULATE (
            SUM ( 'Table'[Sales] ),
            ALLEXCEPT ( 'Table', 'Table'[Product Category] )
        )
    )

    Best Regards