How to use neighboring column as basis for calculation?

[Krib]

I have a table of Store Keys ("Ret. Ch.ID"). Some of the Stores is not R12/LFL. Therefore they have another Store Key ("New Ret. Ch. ID") as a replacement in a neighboring column in the same table. This column basically has a Store Key that's equal if the store is R12/LFL, if not it gets the "New Ret. Ch. ID". I want to get the difference in sales for the stores against past year.

The issue is with the newest store (not LFL). This measure work's:  

#PY Sales =
  CALCULATE (
     SUM ( sales[PY_W_D_Sales_Gross] ),
     ALL ( Store[Retail ch. Region] ),
        TREATAS ( VALUES( Store[New Ret. Ch. ID] ), Store[Ret. Ch. ID] )
       )

The totals doesn't add up and the values doesn't show unless I use the specific "Ret. Ch. ID". Any pointers in the right direction would be great. This is my "simple" test to get the total together (doesnt work):

#test Diff = SUMX(
SUMMARIZE(
    Store,
    Store[Ret. Ch. ID],
    "DIFF", [#Diff Sales]),
    [DIFF]
)

where #Diff sales is:

#Diff Sales = [#Sales]-[#PY Sales]

[v-yiruan-msft]

Hi @Krib ,

Could you please provide some sample data in the tables sales and Store and the final result you expect with calculation logic and special examples? Is there any relationship created between tables sales and Store? If so, could you please provide the relevant relationship information (direction, base field and cardinality etc.). Are you trying to get the difference between the current year's sales value and PY sales value? What is the calculation logic of the PY Sales? Thank you.

In addition, you can refer the following links to get the different of current year and last year sales.

How to compare Last year and Current Year sales in Power BI?

Budget vs Actual vs Last Year – Financial Reporting Tips

Best Regards

[Krib]

The connection for the model/report is:

From the "Sales" (named "Values") a sample:

Ret. Ch. ID	Year	Year No	Year Week	Year Week No	Sales_Gross	PY_W_D_Sales_Gross	Yesterday	_Current week	_Past week	_Comp. Periode
278	2021	2021	2021-W45	202145	41,895		No			YES
278	2021	2021	2021-W45	202145	209,79		No			YES
278	2021	2021	2021-W45	202145	4651,185		No			YES
278	2021	2021	2021-W45	202145	6846,525		No			YES
278	2021	2021	2021-W45	202145	1086,3405		No			YES

From the "Store"-table a sample of the same ID/key:

Retail ch. Region	Country	Ret. Ch. ID	New Ret. Ch. ID	CountryID
Oslo	NO	278	227	1

As mentioned in the initial post i have a measure that calculates the sales:

#Sales = 
 SUM('Values'[Sales_Gross])

 and my atempt for PY sales for this example: 

#PY Sales = CALCULATE(
 SUM('Values'[PY_W_D_Sales_Gross]),
 ALL(Store),
 TREATAS(
 VALUES(Store[New Ret. Ch. ID]),
 Store[Ret. Ch. ID])
)

Then I get the difference with a simple: 

#Diff Sales = [#Sales]-[#PY Sales]

The difference in sales is correct, alone. But when the total appears after including all stores the total diff is significantly off. I've tried an approach that should do the job:

#test Diff = SUMX(
 SUMMARIZE(
 Store,
 Store[Ret. Ch. ID]),
 [#Diff Sales]
)

But here I still end up with a diff. Most likely there are more ID/Keys in the "Values" table then in "Stores" and that messes up the total.

When applying that measure to the next level; Region, the total is more obvious wrong

Where the "#test diff reg" shows the correct total, in this context but not on a country context.

Measure is equal to:

#test Diff = SUMX(
 SUMMARIZE(
 Store,
 Store[Retail ch. Region]),
 [#Diff Sales]
)

So, long story short; How to get replacement (new ret ch id) used for PY sales correctly and how to make it sum up in hierarchy?

[v-yiruan-msft]

Hi @Krib ,

You can update your measure [#PY Sales] as below, please find the details in the attachment.

#PY Sales = 
VAR _tab =
    SUMMARIZE (
        'Values',
        'Values'[Ret. Ch. ID],
        "@pysales",
            CALCULATE (
                SUM ( 'Values'[PY_W_D_Sales_Gross] ),
                FILTER (
                    ALL ( 'Values' ),
                    'Values'[Ret. Ch. ID] = SELECTEDVALUE ( 'Store'[New Ret. Ch. ID] )
                )
            )
    )
RETURN
    SUMX ( _tab, [@pysales] )

Best Regards

[Krib]

this is looks good but it doesnt change depending on date. I see that i didnt mention it was sliced through a date slicer. In your measure I cant see how to iterate through the dates

[v-yiruan-msft]

Hi @Krib ,

Please try to update the formula of measure [#PY Sales] as below and check whether it can update the values of visual dynamically base on the date slicer:

#PY Sales =

VAR _tab =

SUMMARIZE (

'Values',

'Values'[Ret. Ch. ID],

"@pysales",

CALCULATE (

SUM ( 'Values'[PY_W_D_Sales_Gross] ),

FILTER (

ALLSELECTED ( 'Values' ),

'Values'[Ret. Ch. ID] = SELECTEDVALUE ( 'Store'[New Ret. Ch. ID] )

)

)

)

RETURN

SUMX ( _tab, [@pysales] )

Best Regards

[Krib]

Tried before last post but it wont apply to those stores with replacement ID. Thanks for all your effort.