Forum Discussion

jlarques's avatar
jlarques
Helper V
6 years ago
Solved

Total wrong in columns

Hi everyone,

 

I developed a measure that counts the number of users with sanctions accumulated, but on the matrix, the total is wrong, as you can see in the image below.

 

The measure is:

Error column=CALCULATE(DISTINCTCOUNT(Sanctions[Userid]);DATESYTD(CALENDARIO[Date]))
 

The result is:

Value A    30

Value B    21

Value C      6

Value D     1

Total:       52

 

Any ideas about how to solve it?

Thanks to everyone for all your help!

Regards,

 

José Luis 

 

Total wrong in matrix
  • technolog's avatar
    technolog
    2 years ago

    To solve this, you can use a different approach to calculate the totals correctly. One common method is to use a SUMX function that iterates over each row and sums up the distinct counts individually.

    Here's an adjusted measure to get the correct total:

     

    Error column =
    SUMX(
    SUMMARIZE(
    Sanctions,
    Sanctions[Userid],
    "DistinctCount", DISTINCTCOUNT(Sanctions[Userid])
    ),
    [DistinctCount]
    )

1 Reply

  • technolog's avatar
    technolog
    Super User
    2 years ago

    To solve this, you can use a different approach to calculate the totals correctly. One common method is to use a SUMX function that iterates over each row and sums up the distinct counts individually.

    Here's an adjusted measure to get the correct total:

     

    Error column =
    SUMX(
    SUMMARIZE(
    Sanctions,
    Sanctions[Userid],
    "DistinctCount", DISTINCTCOUNT(Sanctions[Userid])
    ),
    [DistinctCount]
    )