cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Divide With a Filter

Quick question.. I'd assume it will be a simple answer. Say I have a table with monthly sales and a table with monthly sales with a visual level filter of some sort.. How could I create a third table showing the percentage of the filtered data of the total amount of sales?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Figured it out.. it was simple as I expected. Still getting comfortable with CALCULATE and FILTER expressions..

 

PercentA = divide(CALCULATE(sum(Sheet1[Sales]),Sheet1[Item Type]="A"),SUM(Sheet1[Sales]))

View solution in original post

6 REPLIES 6
v-haibl-msft
Microsoft
Microsoft

@Anonymous

 

If I understand it correctly, you apply the visual level filter on the second table visual. For example, we have 8 months data as table1, and filter the months as table2. You want to get the percent result (72/293, 90/293 and 12/293), is it right?

 

Divide With a Filter_2.jpg

 

If yes, you can create a Measure with following DAX formula.

Percent_Of_Total = 
DIVIDE (
    SUM ( Table1[Sales] ),
    CALCULATE ( SUM ( Table1[Sales] ), ALL ( Table1 ) )
)

Divide With a Filter_3.jpg

 

Best Regards,

Herbert

Anonymous
Not applicable

@v-haibl-msft

Capture.PNG

Not quite... In a very simplistic form, Say I have the data above. Items A and B and their respective sales. I have a table with total sales, and I have a table with total sales, but filtered to item A. I need to create a measure to calculate the % of total sales (302/637) to create a third table.

the terminology can be confusing because there are tables at the data model and then again tables as a visual.

 

As I understand your post: you have 2 data model tables - let's call them 1 & 2.

 

In your visual you want to dynamically filter Table 1 so it just shows the total of whatever item you select i.e. item A? or is this a permanent calculated value at the data model level?

 

Table 2 sum is to be static/fixed?

 

Is the summed amounts the only info that interested you or do you wish to display all the rows that create the sums?

 

www.CahabaData.com
Anonymous
Not applicable

Figured it out.. it was simple as I expected. Still getting comfortable with CALCULATE and FILTER expressions..

 

PercentA = divide(CALCULATE(sum(Sheet1[Sales]),Sheet1[Item Type]="A"),SUM(Sheet1[Sales]))

@Anonymous

 

If you have got your problem answered, you could mark the right answer as solution to close this thread. Smiley Happy

 

Best Regards,

Herbert

bolabuga
Helper V
Helper V

I dont know for sure if this will help you, but there good examples of the use of "divide" with filters in this thread.

 

http://community.powerbi.com/t5/Desktop/Calculation-between-columns-on-a-table/m-p/73322

 

 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors