Forum Discussion

Newt98's avatar
Newt98
Frequent Visitor
8 years ago

How to use a formula in SubTotal in PowerBI Matrix

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.

Need Help

5 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    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

     

     

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        8 years ago

        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