Divide With a Filter

[Anonymous]

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?

[v-haibl-msft]

@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]

@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.

[CahabaData]

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 · Accepted solution]

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

[v-haibl-msft]

@Anonymous

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

Best Regards,

Herbert

[bolabuga]

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