Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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])
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!
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!