Forum Discussion
Totals in table (matrix)
Hi,
The problem that I have is that the totals in a matrix do not count up correctly in the totals. Other values are correct. With some demo-data I have these values.
As you can see there are mulitple levels within this matrix. The calculation behind this value is a messuement that combines multible other messuements that are made. This is because this value will become a totale messuement of those other values.
The caluclation (redacted) as it stands now is as followed:
Hi Anonymous,
You can create another new measure as below and put this new measure to replace the original measure onto the matrix visual...
Measure = SUMX ( GROUPBY ( 'Table', 'Table'[level1field], 'Table'[level2field] ), [Original measure] )In addition, you can refer the following links to try to solve your problem...
Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand
Dax for Power BI: Fixing Incorrect Measure Totals
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
15 Replies
- tamerj1
Community Champion
Hi Anonymous
If all the columns involved in the matrix hierarchy are from the same table then umyou need to perform a SUMX of the measure over the SUMMURIZE table based on these columns. For example in you matrix looks like you at least 4 columns stacyin the hierarchy of the matrix. Then you need to summarize the table by these 4 columns then iterate over the summary table using SUMX to evaluate and sum the measure over each row of this summary table in order to generate correct subtotals. Something like
SUMX ( SummaryTable, [Measure] )
if the columns are all from different dimension tables then you need to perform the CROSSJOIN of the VALUES of these columns instead of the SUMMARIZE. It really depends on your data model and the existing relationships
- tamerj1
Anonymous
Summarize each table by it's invloved columns then crossjoin the two summary tables.
daXtremeSolution Sage
Anonymous
With this scarce information it's not possible to tell you what's wrong. You have to be more descriptive and actually demonstrate the issue fully. Showing the numbers like this is not enough.
Well I’m trying to calculate the effect (financial) of events. All data in in one table with a column specifying if the values are month, cumulative or last year. The problem is that the green cells with – this demo data – are correct but the red cells are not a addition of the green cells.
This is probably due to the calculation on that level. Picking up raw data and calculating it again. But I need to find a way to make a addition of the total without changing the underlying values. I mean pick up your calculator and add the values up, it does not match.
---
The more deeper calculation is as following:
VAR V1= [calculation1]-[calculation2]
calculation1 = CALCULATE(SUM(Table1'[number1)])/SUM('Table1'[number2]), 'Table1'[string]="Month")
calculation2 =
IFERROR(SUM('table2'[number2])/CALCULATE(SUM('Table2'[number]),'Table2'[String]="Permanent/Fixed employee"),0)
VAR V2= CALCULATE(SUM('Table'[number]),'Tablel'[string]="Month")
VAR V3 = [calculation4]
VAR V31 = SUM('Table1'[number3])
VAR V32 = AVERAGE('Table2'[number2])
VAR V34 = [calculation41]
VAR V35 = [calculation51]
RETURN
V31/(V32*V34*V34)
VAR V4 = [calculation5]
Calculation5 = SUM('Table2'[number4])/SUM('Table2'[number 5])
RETURN
-V1*V2*V3*V4
---
- technolog
Super User
You don't need to use filter in your case
VAR V2= SUM('Table'[number])
Please send screenshots without filter.
- technolog
Hi Anonymous !
It's not enough information, but you can try:
VAR V2= CALCULATE(SUM('Table'[number]),FILTER('Table', 'Table'[string]="Month"))Maybe it helps you) - technolog
What results you get if you just add column to the matrix withous measures?
- technolog
I mean just add all columns from fields panel
Hi Anonymous,
You can create another new measure as below and put this new measure to replace the original measure onto the matrix visual...
Measure = SUMX ( GROUPBY ( 'Table', 'Table'[level1field], 'Table'[level2field] ), [Original measure] )In addition, you can refer the following links to try to solve your problem...
Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand
Dax for Power BI: Fixing Incorrect Measure Totals
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
