DAX formula for share and contribution calculations

[AdityaNayak]

Hello Power BI Community,

Happy New Year! 😊 I require help with DAX calculations in Power BI. Please refer the below image. Columns A, B, and C are from the raw data. I require metrics to be calculated as mentioned in columns "D" and "E" with the corresponding expected values in columns "F" and "G", respectively. Please help!

Thanks in advance,
Aditya Nayak

[v-yangliu-msft]

Hi  @AdityaNayak ,

You can change [Contribution expected value] to this:

Contribution expected value =
var _sum1=CALCULATE(SUM('SumTable'[Sales]),FILTER(ALL('SumTable'),'SumTable'[Geography]=EARLIER('SumTable'[Geography])&&'SumTable'[Index]=EARLIER('SumTable'[Index])))
var _min=MINX(FILTER(ALL(SumTable),'SumTable'[Geography]=EARLIER('SumTable'[Geography])),[Index])
var _sum2=CALCULATE(SUM('SumTable'[Sales]),FILTER(ALL('SumTable'),'SumTable'[Geography]=EARLIER('SumTable'[Geography])&&'SumTable'[Index]=_min))
var _sum3=CALCULATE(SUM('SumTable'[Sales]),FILTER(ALL('SumTable'),'SumTable'[Geography]=EARLIER('SumTable'[Geography])&&'SumTable'[Index]=EARLIER('SumTable'[Index])-1))
return
IF(
    'SumTable'[Mod]=1,DIVIDE(_sum1,_sum2),BLANK()
)

Or this form:

Contribution expected value =
var _sum1=CALCULATE(SUM('SumTable'[Sales]),FILTER(ALL('SumTable'),'SumTable'[Geography]=EARLIER('SumTable'[Geography])&&'SumTable'[Index]=EARLIER('SumTable'[Index])))
var _min=MINX(FILTER(ALL(SumTable),'SumTable'[Geography]=EARLIER('SumTable'[Geography])),[Index])
var _sum2=CALCULATE(SUM('SumTable'[Sales]),FILTER(ALL('SumTable'),'SumTable'[Geography]=EARLIER('SumTable'[Geography])&&'SumTable'[Index]=_min))
var _sum3=CALCULATE(SUM('SumTable'[Sales]),FILTER(ALL('SumTable'),'SumTable'[Geography]=EARLIER('SumTable'[Geography])&&'SumTable'[Index]=EARLIER('SumTable'[Index])-1))
return
IF(
    'SumTable'[Mod]=1,BLANK(),DIVIDE(_sum1,_sum3)
)

Best Regards,

Liu Yang

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

[AdityaNayak]

Hi @v-yangliu-msft ,

Spot on! This is exactly what I require. Thanks a lot! 😊

Regards,

Aditya

[v-yangliu-msft · Accepted solution]

Hi  @AdityaNayak ,

Here are the steps you can follow：

1. In Power query, Add Column – Index Column – From 1.

2. Create calculated column.

Mod = MOD('SumTable'[Index],2)

Share expected value =
var _sum1=CALCULATE(SUM('SumTable'[Sales]),FILTER(ALL('SumTable'),'SumTable'[Geography]=EARLIER('SumTable'[Geography])&&'SumTable'[Index]=EARLIER('SumTable'[Index])))
var _sum2=CALCULATE(SUM('SumTable'[Sales]),FILTER(ALL('SumTable'),'SumTable'[Geography]=EARLIER('SumTable'[Geography])&&'SumTable'[Index]=EARLIER('SumTable'[Index])-1))
return
IF(
    'SumTable'[Mod]=1,BLANK(),DIVIDE(_sum1,_sum2)
)

Contribution expected value =
var _sum1=CALCULATE(SUM('SumTable'[Sales]),FILTER(ALL('SumTable'),'SumTable'[Geography]=EARLIER('SumTable'[Geography])&&'SumTable'[Index]=EARLIER('SumTable'[Index])))
var _min=MINX(FILTER(ALL(SumTable),'SumTable'[Geography]=EARLIER('SumTable'[Geography])),[Index])
var _sum2=CALCULATE(SUM('SumTable'[Sales]),FILTER(ALL('SumTable'),'SumTable'[Geography]=EARLIER('SumTable'[Geography])&&'SumTable'[Index]=_min))
var _sum3=CALCULATE(SUM('SumTable'[Sales]),FILTER(ALL('SumTable'),'SumTable'[Geography]=EARLIER('SumTable'[Geography])&&'SumTable'[Index]=EARLIER('SumTable'[Index])-1))
return
IF(
    'SumTable'[Mod]=1,DIVIDE(_sum1,_sum2),DIVIDE(_sum1,_sum3)
)

3. Result:

Best Regards,

Liu Yang

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

[AdityaNayak]

Hi @v-yangliu-msft ,

Thanks a lot for this solution! 🙂 It works perfectly for "Share expected value".

One minor change that came up just now, though. I want the corresponding rows of "Contribution expected value" to be blank, as those for "Share expected value".

Regards,

Aditya Nayak

[amitchandak]

@AdityaNayak , need to understand data model and raw data

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[AdityaNayak]

Geography	Product	Sales	Share formula	Contribution formula	Share expected value	Contribution expected value
Global	Total Furniture	1000	-	Total Furniture/Total Furniture	-	100%
Global	Manufacturer Furniture	600	Manufacturer Furniture/Total Furniture	Manufacturer Furniture/Total Furniture	60%	60%
Global	Sub-Category Chairs	900	-	Sub-Category Chairs/Total Furniture	-	90%
Global	Manufacturer Chairs	400	Manufacturer Chairs/Sub-Category Chairs	Manufacturer Chairs/Sub-Category Chairs	44%	44%
Global	Sub-Category Tables	700	-	Sub-Category Tables/Total Furniture	-	70%
Global	Manufacturer Tables	500	Manufacturer Tables/Sub-Category Tables	Manufacturer Tables/Sub-Category Tables	71%	71%
India	Total Furniture	100	-	Total Furniture/Total Furniture	-	100%
India	Manufacturer Furniture	80	Manufacturer Furniture/Total Furniture	Manufacturer Furniture/Total Furniture	80%	80%
India	Sub-Category Chairs	90	-	Sub-Category Chairs/Total Furniture	-	90%
India	Manufacturer Chairs	70	Manufacturer Chairs/Sub-Category Chairs	Manufacturer Chairs/Sub-Category Chairs	78%	78%
India	Sub-Category Tables	50	-	Sub-Category Tables/Total Furniture	-	50%
India	Manufacturer Tables	40	Manufacturer Tables/Sub-Category Tables	Manufacturer Tables/Sub-Category Tables	80%	80%