Measure Matrix incorrect total

[Furio_P]

Hello,

I setting up a matrix and populate it wi some measure calculation, like count, sum, percentage, etc. Rows are correct, but total is broken. How I fix it?

[Furio_P]

By the way, this formula performs well when applied to a single selected period (for instance, a month). However, when I set a quarter period (trimester) for two months, the total for the column appears incorrect:

[Furio_P]

Thank you all; in the meantime, I have identified the solution by reading, indeed, various other posts with similar cases. Essentially, I have modified the formulas affected by this issue by adding a 'sumx' at the beginning of the formula for the type of values (categories) in the initial column of the matrix. This way, the totals are correct. For example, the column.

For example, the column "SLA Malus" has seen its formula for measurement modified as follows:

SLA Penale AGV L25 =

SUMX(

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

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]

    ))

)

The bold orange section is the added formula segment in the measurement to obtain a correct result.

Thank you very much for all your guidance; if you have any specific suggestions for this type of solution or not, please let me know. I wouldn't want to have found a solution that isn't actually one.

[v-kaiyue-msft · Accepted solution]

Hi @Furio_P ,

This solution is correct, more details can be found at this link:
Power BI Measure Total Is Incorrect: How To Fix It | Master Data Skills + AI (enterprisedna.co)

Bing Videos

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-kaiyue-msft]

Hi @Furio_P ,

Your solution is great, @Greg_Deckler . I have another idea here, and I wanted to share it for reference.

Incorrect metrics in totals are a relatively common situation where the metrics follow the context of the ‘total’ row and are calculated within that context. As a result, a metric used in the columns of a table visualisation may have unexpected values in the ‘Total’ column.

You can use the following metrics to determine the data level using the IF() + ISINSCOPE() functions, and then perform different calculations depending on the level.

For example, in this measure 2, different results are shown for the date level and the total level. You can adjust the expression to suit your needs.

Measure 2 =
VAR _count = CALCULATE(DISTINCTCOUNT('Table'[Cod. User]),FILTER(ALL('Table'),'Table'[Date] = MAX('Table'[Date]))) //Counts the different user codes for the current date
VAR _table = SUMMARIZE('Table','Table'[Date],'Table'[Cod. User]) // Create virtual table to get table with unique date and user code values
VAR _total_count = COUNTROWS(_table) // Count the number of rows in the virtual table
RETURN
IF(ISINSCOPE('Table'[Date]),_count,_total_count) // Display different data in layers, date layer and total layer display different counts

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[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