topic Re: Measure to calculate sum by comparing 2 columns and reassigning to another row in DAX Commands and Tips

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

pbi_new_user — Mon, 24 Apr 2023 14:37:15 GMT

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

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

v-zhangti — Wed, 26 Apr 2023 06:59:51 GMT

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.

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

pbi_new_user — Wed, 26 Apr 2023 07:06:53 GMT

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.

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

pbi_new_user — Wed, 26 Apr 2023 08:01:30 GMT

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.

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

v-zhangti — Thu, 27 Apr 2023 01:40:11 GMT

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.

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

pbi_new_user — Mon, 01 May 2023 14:11:32 GMT

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.

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

pbi_new_user — Thu, 04 May 2023 09:36:52 GMT

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:

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

v-zhangti — Thu, 04 May 2023 09:45:14 GMT

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.

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

pbi_new_user — Thu, 04 May 2023 10:25:02 GMT

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] )