Forum Discussion

fpennisi17's avatar
fpennisi17
Icon for Helper III rankHelper III
6 years ago
Solved

Column totals for matrix with specific layout

Hi,

I have a matrix with no stepped layout, two row fields (part of a hierarchy), no column fields and multiple value fields.

So basically, except for the two row headers, all the table columns are values.

There are no subtotals.

 

I would like to add a footer row showing the total for some columns.

Is there a way to achieve that?

Need Help
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, fpennisi17 

     

    Based on your description, I created data to reproduce your scenario.

    Table:

     

    You may create measures as below.

     

    Measure1 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value1]),
    BLANK()
)
Measure2 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value2]),
    IF(
        ISFILTERED('Table'[Level]),
        BLANK(),
        SUM('Table'[Value2])
    )
)
Measure3 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value3]),
    BLANK()
)
Measure4 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value4]),
    IF(
        ISFILTERED('Table'[Level]),
        BLANK(),
        SUM('Table'[Value4])
    )
)
Measure5 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value5]),
    IF(
        ISFILTERED('Table'[Level]),
        BLANK(),
        SUM('Table'[Value5])
    )
)

     

     

    Then you need to make the matrix selected, go to 'Visualizations' ribbon, click 'Format'=>'SubTotals', set 'Row subtotals' on.

     

    Result:

     

     

    Best Regards

    Allan

     

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

     

8 Replies

  • HarryBailey's avatar
    HarryBailey
    Icon for Helper II rankHelper II
    6 years ago

    Hi there - are the values in your data source in PowerBI listed as values? I've had this before where they weren't totalling, had to change the type from text to whole number within PowerQuery.

    • fpennisi17's avatar
      fpennisi17
      Icon for Helper III rankHelper III
      6 years ago

      I made a measure for all of them.

      I've set the type for every measure. Some are numeric, some others are textual.

    • fpennisi17's avatar
      fpennisi17
      Icon for Helper III rankHelper III
      6 years ago

       

      In this example I reproduced, in Excel, my matrix layout.

      • v-alq-msft's avatar
        v-alq-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi, fpennisi17 

         

        Based on your description, I created data to reproduce your scenario.

        Table:

         

        You may create measures as below.

         

        Measure1 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value1]),
    BLANK()
)
Measure2 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value2]),
    IF(
        ISFILTERED('Table'[Level]),
        BLANK(),
        SUM('Table'[Value2])
    )
)
Measure3 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value3]),
    BLANK()
)
Measure4 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value4]),
    IF(
        ISFILTERED('Table'[Level]),
        BLANK(),
        SUM('Table'[Value4])
    )
)
Measure5 = 
IF(
    ISINSCOPE('Table'[Field]),
    SUM('Table'[Value5]),
    IF(
        ISFILTERED('Table'[Level]),
        BLANK(),
        SUM('Table'[Value5])
    )
)

         

         

        Then you need to make the matrix selected, go to 'Visualizations' ribbon, click 'Format'=>'SubTotals', set 'Row subtotals' on.

         

        Result:

         

         

        Best Regards

        Allan

         

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