How to optimize my division in a matrix

[Tracy_cai]

Hi all, I hope I can get your help.

As shown in the figure below, each column above the yellow grid I can get from the dimensions of the fact table, and I have calculated the ACT,FC1 under YTD/MTD (ACT,FC1, etc., need to be calculated from the fixed values of the calculate function, for example: ACT = Calculate( [Value], 'rawdata'[scenario] = "ACT")

But when I set up the dimension table for division, I found that every row requires me to calculate with calculate:
For example, to calculate the first column mtd of act, I need to calculate the mtd of act of D first, and then a_MTD_ACT = Calculate( [Value], 'rawdata'[scenario] = "ACT", 'rawdata'[group] = "a"), and again, I need to calculate b,c,d……

Therefore I have multiple measurements to calculate, and I wonder if there is a faster and easier way to calculate them.

Thank you so much!!!!

[v-jingzhang]

Hi @Tracy_cai 

What does your table structure look like? Can you provide some sample data? 

For the first part, if 'rawdata'[scenario] column has all values "ACT", "FC", "PV"..., you can add this column to Column of a matrix visual and add the measure [Value] to Values. You don't need to create several measures with the fixed values. 

For the second part, you can consider using Calculation groups. Please refer to the following blogs:

Top 10 uses for Calculation Groups — Apex Insights: Power BI tips & tricks

Introducing Calculation Groups - SQLBI

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.