cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Dividing column by Grand Total from another column

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. 

Sample Sales Table.jpg

 

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!

3 REPLIES 3
Highlighted
Super User III
Super User III

Re: Dividing column by Grand Total from another column

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?

Highlighted
New Member

Re: Dividing column by Grand Total from another column

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
 

 

 

Highlighted
Community Champion
Community Champion

Re: Dividing column by Grand Total from another column

Hi @p0s01si ,

 

Try this.

 

 

 

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

 

 

 

1.jpg

 

 

 

 

Regards,
Harsh Nathani

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

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.