Forum Discussion

DrillDownBI's avatar
DrillDownBI
Icon for Helper I rankHelper I
1 year ago
Solved

Measure Total, again

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 t...
DAX
matrix
  • DrillDownBI's avatar
    DrillDownBI
    1 year ago

    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]  )  
sanalytics's avatar
sanalytics
Icon for Super User rankSuper User
1 year ago

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