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

    • v-aatheeque's avatar
      v-aatheeque
      Community Support
      1 year ago

      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's avatar
        v-aatheeque
        Community Support
        1 year ago

        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!