SUMX not totaling correctly! PLEASE HELP

[HALEYSTODDARD]

HELP!

My Measure = 

= SUMX(

    VALUES(Dates[Month]),  

    IF(

        NOT(ISBLANK([Total Hours Worked])),  

        DISTINCTCOUNTNOBLANK(Logics[BBS]),

        0

    )

)

It works by month but the total is still wrong. I need the total to only show 32.

[v-dineshya]

Hi @HALEYSTODDARD ,

You need to change your DAX so that the total row is calculated correctly. This usually involves detecting if the context is at the total level and then using a different calculation.

My Measure =
IF(
HASONEVALUE(Dates[Month]),
IF(
NOT(ISBLANK([Total Hours Worked])),
DISTINCTCOUNTNOBLANK(Logics[BBS]),
0
),
DISTINCTCOUNTNOBLANK(Logics[BBS])
)

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @HALEYSTODDARD ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[divyed]

Hello @HALEYSTODDARD ,

You need to modify your dax a bit to solve this issue :

My Measure =
SUMX(
VALUES(Dates[Month]),
VAR _HoursWorked = [Total Hours Worked]
RETURN
IF(
NOT(ISBLANK(_HoursWorked)),
DISTINCTCOUNTNOBLANK(Logics[BBS]),
0
)
)

Alternatively you can try forcing explicit aggregation if above is not working :

My Measure =
SUMX(
SUMMARIZE(
Dates,
Dates[Month],
"@DistinctCount", DISTINCTCOUNTNOBLANK(Logics[BBS])
),
[@DistinctCount]
)

I hope this helps.

Did I answer your query ? Mark this as solution if this helps, Kudos are appreciated.

Warm Regards,

Neeraj

[v-yiruan-msft]

Hi @HALEYSTODDARD ,

You can create a new measure as below based on the existing [BBS Rate], then put this new measure onto the visual to replace the measure [BS Rate]:

BBS Rate =
IF (
    NOT ( ISBLANK ( [Total Hours Worked] ) ),
    DISTINCTCOUNTNOBLANK ( Logics[BBS] ),
    0
)

New measure = SUMX ( VALUES ( Dates[Month] ), [BBS Rate] )

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

Best Regards

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but please try something like below whether it works.

My Measure =
SUMX (
    VALUES ( Dates[Month] ),
    CALCULATE (
        IF (
            NOT ( ISBLANK ( [Total Hours Worked] ) ),
            DISTINCTCOUNTNOBLANK ( Logics[BBS] ),
            0
        )
    )
)