How to use a formula in SubTotal in PowerBI Matrix

[Newt98]

Hi,

I have requirement where I need to show a calculated field like TA% from the below screenshot, which is Actual/Target. The requierment is to show the data as below. 

The problem I am facing is 'TA%' is calculated in backend and hence any aggregations performed in Power BI would be incorrect since the correct value for TA% should always be Actual/Target. The other issue here is, the subtotals do not allow me to write a calculation for TA% and hence it can only perform the preset aggregations allowed by Power BI. Is there a way around it where 

1) I can write a DAX to calculate TA% on the fly 

2) The subtotals show TA% using formula 'Actual/Target'

Table Structure - 

Segment | Model | StockType | PeriodType | Actual | Target | TA% | Stock

Here, I have used 'Unpivot' for Actual, Target, TA% and Stock.

The other way I have tried is by creating the matrix without 'Unpivot', but in that case, I am unable to hide 'Stock' for 'YTD' Period Type. The KPI 'Stock' applies only to MTD and hence I want to hide it for YTD. 

Kindly suggest any alternative to get to my requirement.

I am open to using any other visualizations as well.

Thanks in advance.

[MFelix]

Hi @Newt98,

You should rewrite on DAX the measure in order to have it also calculated based on context in the subtotal something like this:

TA% = DIVIDE(Sum(Stocks[Actuals]), SUM(stocks[Target]))

This is considering you just want a simple division.

For S1 it will give tyou 39/150 = 0.26

This formula will be based on context so it will work on different periods and also on aggregation levels.

Regards,

MFelix

[Newt98]

Hi @MFelix,

Thanks I have tried this and mentioned why I am unable to use this for my requirement here: https://community.powerbi.com/t5/Desktop/How-can-i-hide-Column/td-p/110087/page/2

This works well for the calculation, but I want to hide a few fields like 'Stock' under the YTD - Period Type. This is the main reason why I tried unpivoting the columns.

Please suggest if you have a solution for that?

Thanks,

Tumul

[MFelix]

You can hide the columns in the Matrix visual just reduce the size of the column:

Beware that you problably need to make some adjustments on the owrd wrap and auto-size optionf for row headers, columns headers and values.

Regards,

MFelix

[Newt98]

Thanks for this @MFelix, but this is giving me alignment issues and the headers above are not aligned with the rest of the table.

[MFelix]

Hi @Newt98,

cannot replicate your error when adding levels to the columns and reduce size of columns all my headings are correct, can you post a picture please.

Are you messing around with word wrap and auto size in row, columns and values, your problem may be with that, one of this features may be on or off and impacting the end result.

Regards

MFelix