cancel
Showing results for
Did you mean: 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]))

6 REPLIES 6  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? If yes, you can create a Measure with following DAX formula.

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

Herbert Anonymous
Not applicable

@v-haibl-msft 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.  Memorable Member

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? 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]))  Microsoft

@Anonymous

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

Herbert  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 Announcements #### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates. #### Ted's Dev Camp - July 28, 2022

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