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 faci...
Need Help
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

 

 

Newt98's avatar
Newt98
Frequent Visitor
8 years ago

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

    • Newt98's avatar
      Newt98
      Frequent Visitor
      8 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        8 years ago

        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