Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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?

  • Anonymous's avatar
    Anonymous
    9 years ago

    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

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        CahabaData
        Memorable Member
        9 years ago

        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?