Incorrect Totals for more complex calculations

[ecarg_124]

Hi All,

I am new to Power BI and DAX so would appreciate any help I can get. I working on mix analysis and I am having troubles getting the correct the sum total. I've seen some posts regarding the usage of SUMX, but i couldn't get it to work. Hoping to get some support from the experts in this community! Appreciate it! 

Raw data looks like this:

REGION	SEGMENT	SKU	VOLUME BUDGET	VOLUME	NET REVENUE BUDGET	NET REVENUE
Region1	Segment1	Sku1
Region1	Segment1	Sku2
Region1	Segment1	Sku3
Region1	Segment1	Sku4
Region1	Segment2	Sku1
Region1	Segment2	Sku2
Region1	Segment2	Sku3
Region1	Segment2	Sku4
Region2	Segment1	Sku1
Region2	Segment1	Sku2
Region2	Segment1	Sku3
Region2	Segment1	Sku4
Region2	Segment2	Sku1
Region2	Segment2	Sku2
Region2	Segment2	Sku3
Region2	Segment2	Sku4
		TOTAL

This is how I would like to see the output:

	Segment Mix
Region1
Segment1
Segment2
Region2
Segment1
Segment2
Total

Segment Mix = ( ([VOL]/[VOL (Total Region)]) - ([BU VOL]/[BU VOL (Total Region)]) ) *
([NR/unit] - ([NR (Total Region)]/[VOL (Total Region)])) *
([BU VOL (Total Region)]/[BU VOL (All Regions)])

The segment mix measure works for each line except for the TOTAL. For the Total, i need the sum of the segment mix for each line. 

Here are other measures used:

VOL= sum(DATA[VOLUME])

VOL (Total Region) = CALCULATE([VOL],ALL(PRODUCT_LOOKUP[SEGMENT]))

BU VOL= sum(DATA[VOLUME BUDGET])

BU VOL (Total Region) = CALCULATE([BU VOL],ALL(PRODUCT_LOOKUP[SEGMENT]))

BU VOL (All Regions) = CALCULATE([BU VOL],ALL(REGION_LOOKUP[REGION]),ALL(PRODUCT_LOOKUP[SEGMENT]),ALL(PRODUCT_LOOKUP[SKU]))

NR=sum(DATA[NET REVENUE])

NR/unit = NR / VOL

NR (Total Region) = CALCULATE([NR],all(PRODUCT_LOOKUP[SEGMENT]))

Thank you all!

[amitchandak]

@ecarg_124 

Divide work best when you sum(A)/Sum(B). Multiplication works best when you have Sum(A*B)

In you case it Sum(A)*Sum(B). In this case you have to use Summarize or values to have multiplication context  and sum and Avg after that

Something like this

sumx(Summmarize(Dim[Date1],"M1",[measure1],"M2",[Measure2]),[M1]*[M2])

[ecarg_124]

Thanks for the quick response! I think i came across another issue with the Summarize function this time, which is affecting the overall SUMX results. 

As part of my formula, I need to include the SALES TOTAL REGION number. This is what I am getting when I do a simple matrix & measure. The values here are correct.

MATRIX RESULTS
REGION	SALES	SALES TOTAL REGION
REGION1	20,000	105,500
REGION2	50,000	105,500
REGION3	35,500	105,500
TOTAL	105,500	105,500

SALES = sum(Actual Sales)

SALES TOTAL REGION = CALCULATE (sum(Actual Sales), ALL(DATA [REGION]), ALL (DATA [SEGMENT]), ALL(DATA [SKU]))

However, when I create a Table using summary, this is what I am getting instead:

SUMMARIZE RESULTS
REGION	SALES2	SALES TOTAL REGION2
REGION1	20,000	20,000
REGION2	50,000	50,000
REGION3	35,500	35,500
TOTAL	105,500	105,500

Table = SUMMARIZE ( DATA, DATA[REGION],

"SALES2", sum(Actual Sales),

"SALES TOTAL REGION2", CALCULATE (sum(Actual Sales), ALL(DATA [REGION]), ALL (DATA [SEGMENT]), ALL(DATA [SKU]))

Not sure what I am doing wrong. Thank you again for the help!

[ecarg_124]

@amitchandak 

Thanks for the quick response! I think i came across another issue with the Summarize function this time, which is affecting the overall SUMX results. 

As part of my formula, I need to include the SALES TOTAL REGION number. This is what I am getting when I do a simple matrix & measure. The values here are correct.

MATRIX RESULTS
REGION	SALES	SALES TOTAL REGION
REGION1	20,000	105,500
REGION2	50,000	105,500
REGION3	35,500	105,500
TOTAL	105,500	105,500

SALES = sum(Actual Sales)

SALES TOTAL REGION = CALCULATE (sum(Actual Sales), ALL(DATA [REGION]), ALL (DATA [SEGMENT]), ALL(DATA [SKU]))

However, when I create a Table using summary, this is what I am getting instead:

SUMMARIZE RESULTS
REGION	SALES2	SALES TOTAL REGION2
REGION1	20,000	20,000
REGION2	50,000	50,000
REGION3	35,500	35,500
TOTAL	105,500	105,500

Table = SUMMARIZE ( DATA, DATA[REGION],

"SALES2", sum(Actual Sales),

"SALES TOTAL REGION2", CALCULATE (sum(Actual Sales), ALL(DATA [REGION]), ALL (DATA [SEGMENT]), ALL(DATA [SKU]))

Not sure what I am doing wrong. Thank you again for the help!