Dividing column by Grand Total from another column

[p0s01si]

So I have store sales numbers for this year and last which allow me to easily calculate YOY%. I am now trying to get a sales contribution column. By this I mean, in terms of bps, how much each store contributed to the region's sales growth. I've attached a basic sample table in excel where I've easily calculated this. 

In Power BI, I am using slicers/filters because we have numerous regions etc. I created a measure that uses the calculate function but then it doesn't adjust dynamically based on the filters. I truly appreciate any help!

[harshnathani]

Hi @p0s01si ,

Try this.

Sales Contribution = 
IF(ISINSCOPE(Geography[Store]),DIVIDE([YOY Difference], CALCULATE([Total Sales],ALL(Geography[Store]))))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi,

You can create a region total calculated column like below.

Region Total = var region = 'Table'[Region] return CALCULATE(SUM('Table'[Sales LY]),FILTER('Table','Table'[Region]=region))

and then create a sales contribution column

Sales Contribution = 'Table'[Delta]/'Table'[Region Total]

You can check it will work with both region/store slicer.

Please like if it helped you.

Thanks,

Sumeet

[sturlaws]

Hi,  you are refering to a measure which uses the calculate function, which is a bit unclear. Could you post the code for this measure?