Values are correct but Totals are NOT

[Anonymous]

Hello. Any help is appreciated.
There are 2 tables:
1. RPT Ecommerce US Share Report

2. RPT Ecommerce US Share Price Tier Instacart
The logic behind a measure is if Account ="Instacart" then take value from  RPT Ecommerce US Share Price Tier Instacart, otherwise from RPT Ecommerce US Share Report.
The 1st part of a measure (SELECTEDVALUE('RPT Ecommerce US Share Report'[Manufacturer]) = "BEAM SUNTORY", [BSI Retail Sales 4W],) is working fine, but 2nd part is wrong. Values in my table visual showing correct results, but Totals is wrong (see screenshot).

Here is a measure I'm using:

Retail Sales 4W = IF(

    SELECTEDVALUE('RPT Ecommerce US Share Report'[Manufacturer]) = "BEAM SUNTORY", [BSI Retail Sales 4W],

     IF(SELECTEDVALUE('RPT Ecommerce US Share Report'[Account]) = "Instacart", SUM('RPT Ecommerce US Share Price Tier Instacart'[Total Price Tier Dol 4W]),

         SUM('RPT Ecommerce US Share Report'[Dol 4W]) )

)

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

You can modify the DAX formula to the following:

Measure =
var _table=
SUMMARIZE(
    'RPT Ecommerce US Share Report','RPT Ecommerce US Share Report'[Account],"Value",[Retail Sales 4W])
return
IF(
    HASONEVALUE('RPT Ecommerce US Share Report'[Account]),
    [Retail Sales 4W],SUMX(_table,[Value]))

Refer to:

HASONEVALUE function (DAX) - DAX | Microsoft Learn

Best Regards,

Liu Yang

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

[Anonymous]

DAX Expression you've provided working perfectly fine. Thank you so much for your help.

[DarkArchonNL]

Hi Pulyk,

Try this:

Retail Sales 4W =
VAR SelectedManufacturer = SELECTEDVALUE('RPT Ecommerce US Share Report'[Manufacturer])
VAR SelectedAccount = SELECTEDVALUE('RPT Ecommerce US Share Report'[Account])
VAR DefaultSum = SUM('RPT Ecommerce US Share Report'[Dol 4W])
VAR InstacartSum = SUM('RPT Ecommerce US Share Price Tier Instacart'[Total Price Tier Dol 4W])
RETURN
    IF (
        SelectedManufacturer = "BEAM SUNTORY",
        [BSI Retail Sales 4W],
        IF (
            SelectedAccount = "Instacart",
            InstacartSum,
            DefaultSum
        )
    )

[Anonymous]

Thanks for helping out @DarkArchonNL .
Unfortunately, your query gives the same results as mine. It is showing correct values in visual for each Account, but Totals still incorrect. Totals does not count values for "Instacart", which is from a different table.

[DarkArchonNL]

I see. Your issue is occurring because the measure isn't properly aggregating the total across the two different tables. When the measure is calculated for each row, the context of the row includes the account, so it works fine. But when the measure is calculated for the total, there's no context of the account. Hence, it is not able to calculate the "Instacart" values.

In order to fix this issue, you might need to calculate the two sums separately and then add them together. Here's an example of how you could modify your measure to do that:

Retail Sales 4W Total =
VAR InstacartSum = CALCULATE(
SUM('RPT Ecommerce US Share Price Tier Instacart'[Total Price Tier Dol 4W]),
'RPT Ecommerce US Share Report'[Account] = "Instacart"
)
VAR OtherSum = CALCULATE(
SUM('RPT Ecommerce US Share Report'[Dol 4W]),
'RPT Ecommerce US Share Report'[Account] <> "Instacart",
'RPT Ecommerce US Share Report'[Manufacturer] <> "BEAM SUNTORY"
)
VAR BeamSuntorySum = [BSI Retail Sales 4W]
RETURN
BeamSuntorySum + InstacartSum + OtherSum

In this version, we are creating three variables: `InstacartSum`, `OtherSum`, and `BeamSuntorySum`. The `CALCULATE()` function changes the context in which the SUM is computed, filtering the data to only include rows where the account is "Instacart" for the `InstacartSum`, and where the account is not "Instacart" and the Manufacturer is not "BEAM SUNTORY" for the `OtherSum`. `BeamSuntorySum` remains the same as your original measure.

After calculating these three sums under the desired conditions, we simply add them together for the final result. This measure should now calculate the correct total in your visual, while leaving the individual row values unchanged.

[Anonymous]

Doesn't show correct values either and Individual row actually changed as well, addition in the end (BeamSuntorySum + InstacartSum + OtherSum) is affected individual row as well.