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...
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.

     

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

It worked for the data.

I didn't want the intermediate subtotals, so I also unchecked them for first level of hierarchy, by enabling "per row level" option.

In this way, I had to apply the condition you suggest (if not in scope then blank) only in the measures for which I don't want the total.

 

By the way, one annoying thing remains. I don't know why, but albeit the totals take only the height of one row, the footer row is much higher (see screenshot)

 

 

How can I eliminate that space?

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

Hi, fpennisi17 

 

I wonder if you have set some other formats. You may try 'Revert to default' and then reset them. Here is my setting in 'Subtotals'.

 

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.

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

    I understood the issue.

    In my table there is a value column which is hidden, that I use for sorting.

    I forgot to apply the condition on the scope on that column, so the subtotal of that column spanned taking that space.

    I just need to apply that condition and everything worked like a charm.

     

    Thank you very much!