cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Newt98 Frequent Visitor
Frequent Visitor

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'

 

Capture2.PNG

 

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.

5 REPLIES 5
Super User III
Super User III

Re: How to use a formula in SubTotal in PowerBI Matrix

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

 

 

 

rtrrr.png

 

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

 

Regards,

MFelix

 

 


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Newt98 Frequent Visitor
Frequent Visitor

Re: How to use a formula in SubTotal in PowerBI Matrix

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

Super User III
Super User III

Re: How to use a formula in SubTotal in PowerBI Matrix

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

 

hide.gif

 

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


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Newt98 Frequent Visitor
Frequent Visitor

Re: How to use a formula in SubTotal in PowerBI Matrix

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

Super User III
Super User III

Re: How to use a formula in SubTotal in PowerBI Matrix

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

 

 

 


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors