Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Newt98
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
MFelix
Super User
Super User

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Newt98
Frequent Visitor

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

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Newt98
Frequent Visitor

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

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 Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.