Measure Total, again

[DrillDownBI]

Hi,

this is my third day struggling with measure total, time to get some help.

I have 3 columns in my matrix tabel that should be summarized row by row, and then ofcource sum up everything in the total.

Column 1 [PY Actual] is Value from my fact table, column 2 [Inflation_basic] is a value from Inflation table, this tables have dates and Items. Column 3 [Inflation Effect] is Column 1 * Column 2 and gives me correct row value but then sum Total * Total and thats were the problem is. Item and Calendar is the dim tables.

I have try summarize, summarize with sumX, hasonevalue, but no luck.

[DrillDownBI · Accepted solution]

I finally find a solution for this.

I use summarize in a temp table and then sumx like below DAX code.

VAR _stdInflation =

                Summarize(

                    'Calendar', --Table

                    'Calendar'[Period], --Group by

                        "@value", --name of expression                    

                        CALCULATE(

                            SUM(FactTable[ActualPrel]),

                                SAMEPERIODLASTYEAR('Calendar'[Period])

                        ) ,

                        "@value2",    

                             CALCULATE(

                                       SUM(Summary[Inflation])                      

                             )

                )

                          RETURN SUMX(_stdInflation ,[@VALUE] *[@value2]  )  

[v-aatheeque]

Hi @DrillDownBI ,

Thank you so much for sharing your solution.

It’s great that you included your DAX code here, as it could really help other community members facing the same challenge.

Feel free to keep exploring and posting your questions or solutions here in the Fabric Community your contributions are truly valuable!

[sanalytics]

@DrillDownBI 

Whatever logic or calculation you’ve applied row by row in your matrix visual will also be applied to the Total. That’s how DAX operates, and it’s not incorrect by design.For example, the value 5,458,026 is calculated simply by multiplying Column 1 and Column 2 at each row level. However, if you want to compute an accurate total for Column 3, you need to wrap your calculation in an iterative function like SUMX.

SUMX(VALUES(Table[ITEM]), [INFLATION EFFECT])
One suggestion for you Don’t just copy and paste the formula,take time to understand the concept behind it. That will save you trouble later on.
If the formula doesn’t produce the expected results, I recommend sharing some dummy data along with your .pbix file so the logic can be verified more precisely.

Regards

sanalytics

[FBergamaschi]

You need to go Item by Item, so that will be done at the total as well, otherwise at the total you are multiplying the two totals of the other two measures

to do this you need to iterate VALUES ( Item) like

SUMX ( VALUES ( item ), [PY ACtual] * [Inlation basic] )

Not sure you need to SUM (SUMX) or AVERAGE (AVERAGEX ) the result

Hope this helpes, to help more please provide tables or pbix file

please give kudos or mark as a solution in case

Thanks!

best

[DrillDownBI]

Thanks,

but no luck with VALUES, I´m dealing with different granularity from all 4 tables.

Calendar have Period "yyyy-mm"
Fs Item have Item
Salary have Item and Period and is a SCD2 table

Fact have Item and Period.

Also test crossfilter, userelationship, crossfilter direction to both, everything mess up the total.

Some report should stay in excel 🙂

I cant merge tables because Inflation will affect on PY Actuals, so 2024-06 YTD * 2025-06 Inflation.
Can´t provide pbix.

[FBergamaschi]

The only way to help you then is that you provide dummy data for all the tables and desired results so we can import them, otherwise it is impossible to help you

[v-aatheeque]

Hi @DrillDownBI 

  as suggested Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Regards,
Atheeq.

[v-aatheeque]

Hi @DrillDownBI 

 I wanted to gently remind you about our request for sample data related to your issue. Providing this data in a usable format (not as a screenshot) will greatly help us understand your question and work towards a solution.

Please ensure that the sample data covers your issue completely and does not include any sensitive or unrelated information. Additionally, if you could include the expected outcome based on the sample data, that would be very helpful.

Thank you for your assistance and I look forward to your response!

[v-aatheeque]

Hi @DrillDownBI 

I wanted to kindly follow up on our request for sample data related to your issue. Having access to this data in a usable format (rather than a screenshot) will significantly help us understand the problem and work toward a solution more effectively.

To ensure we can assist you efficiently, please make sure the sample data :

• Clearly reflects the issue you're experiencing.
• Is free from any sensitive or unrelated information.
• Includes the expected outcome based on the data provided.
  
  Your cooperation is greatly appreciated, and we look forward to your response.

Regards,
Atheeq.