Forum Discussion

DrillDownBI's avatar
DrillDownBI
Helper 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 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.

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]  )  

9 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    1 year ago

    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's avatar
      DrillDownBI
      Helper I
      1 year ago

      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's avatar
        FBergamaschi
        Super User
        1 year ago

        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

  • sanalytics's avatar
    sanalytics
    Super 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

  • DrillDownBI's avatar
    DrillDownBI
    Helper I
    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]  )  
    • v-aatheeque's avatar
      v-aatheeque
      Community Support
      1 year ago

      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!