Forum Discussion
Helper IIncorrect Totals for more complex calculations
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!
3 Replies
- amitchandak
Super User
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_124Thanks 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
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!
