Power BI Matrix Woes: Understanding Total Discrepancies Across Time Periods

[Furio_P]

Good morning everyone,
I apologize for disturbing everyone again, but I'm literally going crazy with the totals in Power BI matrices. I've created measures to, obviously, perform calculations based on numbers and proportions between some data sources, sharing cities. So, through the matrix table and the use of SUMX syntax to direct the iteration of measures in the unique table containing the locations, I obtained the correct results, even in the column totals.

However, if I now add other time periods to the matrix table (for example, I insert months to see the data for the same month), I get the data for individual cities and the row (month) totals correctly, but the column totals are literally out of this world. I've tried various approaches, always using syntaxes like summarize and adding dates in addition to cities, but nothing works. The result obtained then becomes totally different in individual months (it has a correct overall total, but then the value of individual months becomes incorrect).

I can't figure out what could be causing this... Thank you very much for any help.

[Furio_P]

I think the issue is in those measure: 

SLA Bonus AGV L25 =

SUMX(

    VALUES('Siti Attività'[Sito Area]),

IF(

    [SLA AGV L25] <= 0.92,

    BLANK(),

    IF(

        [SLA AGV L25] <= 0.95,

        0.03 * [Costo Lotto L25 AGV],

        IF(

            [SLA AGV L25] <= 0.97,

            0.04 * [Costo Lotto L25 AGV],

            IF(

                [SLA AGV L25] <= 1,

                0.08 * [Costo Lotto L25 AGV],

                BLANK()

            )

        )

    )

))

and 

SLA Penale AGV L25 =

    SUMX(

        VALUES('Siti Attività'[Sito Area]), -- Itera su ciascun sito

        IF(

            [SLA AGV L25] <= 0.92,

            IF(

                (0.92 - [SLA AGV L25]) * [Costo Lotto L25 AGV] < 0,

                [Costo Lotto L25 AGV],

                (0.92 - [SLA AGV L25]) * [Costo Lotto L25 AGV]

            )

        )

    )

[Furio_P]

I tried to isolate the various cities, but the response is confusing, I can't find a common element. For example, here, isolating just one city, where all the values are present, it adds them up correctly, but it doesn't even calculate a single value:

Other times, however, it still performs additions even if the data is singular.:

[Greg_Deckler]

@Furio_P First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[johnbasha33]

@Furio_P 

Here are some steps you can take to troubleshoot and potentially resolve the issue:

1. Check Measure Aggregation: Ensure that your measures are aggregating correctly at the desired level. For example, if you're calculating totals for cities, make sure your measure is using functions like SUMX or CALCULATE to aggregate data at the city level.

2. Verify Matrix Aggregation: Double-check the aggregation settings of your matrix visual to ensure it's aggregating data correctly across both cities and time periods. You may need to adjust the settings to align with the granularity of your measures.

3. Review Relationship and Cross Filtering: Check the relationships between your tables and the cross-filtering behavior to ensure that data is being filtered correctly across cities and time periods. Incorrect filtering can lead to incorrect totals.

4. Use DAX Functions Appropriately: Make sure you're using DAX functions like SUMX, CALCULATE, and FILTER appropriately in your measures to handle filtering and aggregation logic correctly.

5. Test with Simpler Visuals: Simplify your report by testing the measures and matrix visuals with fewer dimensions (e.g., only cities or only time periods) to isolate the issue. This can help identify if the problem is related to the interaction between multiple dimensions.

6. Check Data Model and Relationships: Review the data model and relationships between tables to ensure they accurately reflect the relationships between cities, time periods, and other relevant dimensions.

7. Use DAX Studio for Debugging: If you're still having trouble identifying the issue, consider using DAX Studio to debug your measures and evaluate their behavior at different aggregation levels.
   
   
   Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!