Struggling to display correct Total using Measures

[JustinDoh1]

Hi community,

(I revised this post (from yesterday's original) to be more simpler).

I have uploaded revised PBIX file here.

I also uploaded underlying dataset here.

Currently, total for "FrcstWtdScore" is displaying 65 instead of 105.

Bottom is detail of that measure:

FrcstWtdScore =

IF  (  vw_NH_HealthCitations_NM[FrcstMths] < 13 && ( vw_NH_HealthCitations_NM[Standard Deficiency_M] = "0" ) ,  

        sumx (

             values(vw_NH_HealthCitations_NM),

            [FrcstPointsAdj] * (0.75)

            ),

        sumx (

              values(vw_NH_HealthCitations_NM),

              [FrcstPointsAdj] * (0.25)

        )    

     )

When you add the total of each row on a csv file dataset (using SUM), it is 105, and that is what I am trying to get as a result.

I am not sure what the logic of getting 65 is.  If anyone tell me how 65 got calculated, I would appreciate it.

Thank you for help!

[v-sshirivolu]

Hi @JustinDoh1 ,
Thanks for reaching out to Fabric community Forum.
I have replicated this scenario on my end using sample data with the columns: ProviderID, FrcstMths, Standard Deficiency_M, FrcstPointsAdj.
Below is an solution that ensures correct calculations at both the row and total levels:

FrcstWtdScore =
IF (
    SELECTEDVALUE(vw_NH_HealthCitations_NM[FrcstMths]) < 13 &&
    SELECTEDVALUE(vw_NH_HealthCitations_NM[Standard Deficiency_M]) = 0,
    SUM(vw_NH_HealthCitations_NM[FrcstPointsAdj]) * 0.75,
    SUM(vw_NH_HealthCitations_NM[FrcstPointsAdj]) * 0.25
)

Forecast C2/3 Wtd Issue =
IF (
    HASONEVALUE(vw_NH_HealthCitations_NM[ProviderID]),
    [FrcstWtdScore],
    SUMX (
        VALUES(vw_NH_HealthCitations_NM[ProviderID]),
        CALCULATE([FrcstWtdScore])
    )
)

Thank you.

[JustinDoh1]

@v-sshirivolu Thank you for your help.

Sorry. I revised the original post to be more simple.

Can you please try using my PBIX file attached (instead of using sample data)?

Thanks.

[v-sshirivolu]

Hi @JustinDoh1 ,

The original total was 65.00 because the IF condition in the measure failed in the Grand Total (due to multiple column values), causing the logic to default to the 0.25 multiplier on all 260 points.

The fix was a Single Robust SUMX Measure that forced the calculation to evaluate row-by-row, bypassing the context issue.

FrcstWtdScore =

SUMX(

    vw_NH_HealthCitations_NM,  

    VAR FrcstMths = vw_NH_HealthCitations_NM[FrcstMths 3h]

    VAR FrcstPoints = vw_NH_HealthCitations_NM[FrcstPointsAdj 3j]

    RETURN

        IF(

            FrcstMths = 12, 

            FrcstPoints * 0.75,

            FrcstPoints * 0.25

        )

)

This measure also corrected the business logic by using the FrcstMths = 12 criteria to isolate the 80 points required for the 0.75 multiplier, resulting in the correct total of 105.00.

 
Please find the attached .pbix file for your reference.

[JustinDoh1]

@v-sshirivolu Thank you for your help.

Is this some limitation of PowerBI? I am still trying to understand what you meant by "multiple column values". When you say "context issue", can you elaborate little bit please?

[v-sshirivolu · Accepted solution]

Hi @JustinDoh1 ,

This isn’t really a limitation of Power BI, it’s just how DAX calculates things differently for individual rows versus totals. Each row looks at only that row’s data, while the total row looks at all the rows together, which is why the numbers can sometimes seem off. When I mentioned “multiple column values,” I meant that if your measure uses more than one column, Power BI may not know which value to pick for each row unless the calculation is written explicitly. A simple way to handle this is to use SUMX to calculate values row by row and HASONEVALUE if you need to treat totals differently. Doing this ensures that both the row-level values and the totals display correctly.

[Praful_Potphode]

Hi @JustinDoh1 ,

There are a lot of nested measures being used here in the pbix.my advice to you will be to create a card visual and put the first base measure on which all conditions are written and then from there investigate the value.if the base measure will come proper, the number will come correct.

Thanks and Regards,

Praful

[JustinDoh1]

@Praful_Potphode

Thank you for your comment.

I revised the original post to be more simple version.

Can you please try using my PBIX file attached?

Thanks.