Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Creating a measure to divide a column by a measure

Hi All,

 

I have created a measure that allows me to see the total sales for all stores that have a rate that is not blank.  This report will be focused on a region, so a slicer will decide which region will be showing.  Now, I would like to divide the stores that are not blank's total sales by the total for the region.  This will leave me with a percent of the of the sales that are reporting rates for each store that is reporting a rate.

 

Here's my first measure to get the total sales of stores that do not have a blank rate: 

Weighted Total = (CALCULATE(SUM('Main'[Total Sales]),FILTER(Main,VALUE('Main'[Total Rate])<> BLANK())))
 
StoreTotal SalesTotal RateRegion

A

100.021
B150.041
C125.021
D50-1
E100-1
F200.072
G110.012
H60-2
I75.052
 
 In the sample data, my first measure would exclude stores D & E for Region 1, and the total sales would be 375.  I would then want to add a column to this table that would divide the store's total sales (if they reported a rate) by the regional total.  For example, it would do the 100/375 calculation for Store A and leave me with 27%. in another column in the table.
 
Thanks in advance
2 REPLIES 2
AlexisOlson
Super User
Super User

Just to be clear, you cannot use slicer selections in a calculated column. Calculated columns and calculated tables cannot be responsive to slicers since they are only computed once per data load.

Anonymous
Not applicable

I think I misspoke a bit in the last paragraph.  I'm not looking for a calculated column, but rather using a table visual to display this data.  I would want to add that measure as a field in my table visual.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors