Comparison field does not update when I SUM a value.

[DCZ]

I would love some help on figuring out this issue that has been bugging me for days.

I have 3 User IDs comparing the current number with the average number.

User ID	Average Num	Current Num	Comparison
1	200	150	Below Avg
1	200	150	Below Avg
2	200	300	Above Avg
3	100	200	Above Avg

In Power BI desktop I SUM(Current Num) and the table will look like this.

User ID	Average Num	Current Num	Comparison
1	200	300	Below Avg
2	200	300	Above Avg
3	100	200	Above Avg

The User ID, Average Num, and Current Num updated. Comparison did not update, as, it should be Above instead of Below.

The Comparison column was created using DAX if Current Num > Average Num then Above Avg and if Current Num < Average Num then Below Avg.

I also want to note that there are many columns in the table and not just User ID.

[v-weiyan1-msft · Accepted solution]

Hi @DCZ ,

@amustafa nice method! Thank you, for your quick response and the solution provided.
And you can also consider using the following code to create a Measure.

Comparison = 
VAR Cnt =
    CALCULATE (
        COUNT ( 'Table'[User ID] ),
        FILTER ( 'Table', 'Table'[User ID] = MAX ( 'Table'[User ID] ) )
    )
VAR Sum_Num =
    CALCULATE (
        SUM ( 'Table'[Average Num] ),
        ALLEXCEPT ( 'Table', 'Table'[User ID] )
    )
VAR Avg_Num =
    IF ( Cnt > 1, MAX ( 'Table'[Average Num] ), Sum_Num )
VAR Cur_Num =
    CALCULATE (
        SUM ( 'Table'[Current Num] ),
        ALLEXCEPT ( 'Table', 'Table'[User ID] )
    )
RETURN
    IF ( Cur_Num > Avg_Num, "Above Avg", "Below Avg" )

Result is as below.

Best Regards,
Yulia Yan

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

[amustafa]

try this...

ComparisonStatus = 
VAR CurrentNum = SUM(Table1[Current Num])
VAR AverageNum = AVERAGE(Table1[Average Num])
RETURN
    IF(
        HASONEVALUE(Table1[User ID]),
        IF(
            CurrentNum > AverageNum, "Above Average",
            IF(
                CurrentNum = AverageNum, "Equal",
                "Below Average"
            )
        ),
        BLANK() -- This is for the total row where multiple User IDs are aggregated
    )

If I answered your question, please mark this thread as accepted and Thums Up!
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/

[DCZ]

Thanks for replying! I tried the code and the data is blank for me on each row. There are around 10 columns with other IDs and Dates. I tried removing HASONEVALUE and it is still not working for me.