Measure to calculate sum by comparing 2 columns and reassigning to another row

[pbi_new_user]

Hi,

I have a table with sales amount for different stores along with the storenumber from where the sale originated.

Country	Store	Store original	Amount
A	S1	O1	100
A	S1		100
A	S2	O1	100
A	S2		50
A	S2	O1	50
A	O1		100
		Total	500
B	S1	O2	100
B	S1		100
B	S2	O2	100
B	S2	O2	50
B	O2		100
		Total	450

I need to create a measure to calculate the sales for each corresponding store and the output needs to be like below,

Country	Store	total Sales
A	S1	100
A	S2	50
A	O1	350
	Total	500
B	S1	100
B	S2
B	O2	350
	Total	450

[v-zhangti]

Hi, @pbi_new_user 

You can try the following methods.

Column:

New Store Column = IF([Store original]<>BLANK(),[Store original],[Store])

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[pbi_new_user]

Thanks for the quick reply @v-zhangti .

This will work, but I cannot create a calculated column as I connect to a tabular model. Also I need to also calcualte the original sales data along with the corrected sales like below,

Country	Store	Sales Amount	Corrected Sales
A	S1	200	100
A	S2	200	50
A	O1	100	350
B	S1	200	100
B	S2	150
B	O2	100	350

So I need to write this as a measure.

[v-zhangti]

Hi, @pbi_new_user 

You can try the following methods.
Measure:

Sales Amount = SUM('Table'[Amount])

Measure 1 = 
Var _table=FILTER(SUMMARIZE('Table','Table'[Country],'Table'[Store original],"Sum",SUM('Table'[Amount])),[Store original]<>BLANK())
Return
SUMX(_table,[Sum])

Measure 2 = SUMX(FILTER(ALL('Table'),[Country]=MAX('Table'[Country])),[Measure 1])

Measure 3 = MAXX(FILTER(ALL('Table'),[Country]=MAX('Table'[Country])),[Store original])

Corrected Sales = 
Var _N1=CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),[Store original]=BLANK()&&[Country]=MAX('Table'[Country])&&[Store]=MAX('Table'[Store])))
Return
IF(MAX('Table'[Store])=[Measure 3],[Sales Amount]+[Measure 2],_N1)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[pbi_new_user]

Hi @v-zhangti ,

I managed to replicate these measures in my data model but only issue is that I want to also see the totals on the country level which is wrong or not showing up. Please help.

My model:

Result in matrix visual: 

[v-zhangti]

Hi, @pbi_new_user 

You can try the following methods.
Measure:

Result = Var _table=SUMMARIZE('Table','Table'[Country],'Table'[Store],"Sum",[Corrected Sales])
Return
IF(HASONEVALUE('Table'[Store]),[Corrected Sales],SUMX(_table,[Sum]))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[pbi_new_user]

Thanks for the help @v-zhangti .

I solved this by creating one measure instead of so many measures and my solution is much cleaner.

Correct Sales =
sum(Sales[Amount])
   + SUMX (
        FILTER (
            CALCULATETABLE ( 'Sales', REMOVEFILTERS ( 'Store' ) ),
            CONTAINS ( 'Store', [Store], 'Sales'[Store Original] )
        ),
        [Amount]
    )
   - SUMX (
        FILTER ( 'Sales', 'Sales'[Store Original] <> BLANK() ),
        [Amount]
    )

[pbi_new_user]

Thanks for the solution @v-zhangti . 

My data is really huge and sales table only has the store and store_original columns. The country column exists in a seperate store dimension. I am not able to replicate these measures there.

[pbi_new_user]

Hi @v-zhangti ,

To simplify the above scenario, this is how my sales table looks like,

 And I want the output as below,

Store	Sales Amount
S1	20
S2	30
S3	40
S4	50
O1	80
O2	60

Many thanks for your help.