Forum Discussion

Oros's avatar
Oros
Post Prodigy
1 year ago
Solved

Filtered measure

Hello,

 

I have the following table. What would be the correct measure to use so that if a year and salesperson is selected, the specific salesperson's sales (for the selected year) will be calculated against the total sales for the selected year?  Thanks.

 

For example, if 2021 is selected and Bob is selected:

Total Sales = $150

Bob's Sales = $50

 

(need help in this mesure below)

Measure = $50 / $150 = 33.33%

 

 

 

Need Help
  • Irwan's avatar
    Irwan
    1 year ago

    hello Oros 

     

    please check if this accomodate your need.

     

    create a new measure with following DAX.

    Measure = 
    var _Salesperson = SELECTEDVALUE('Table'[SALESPERSON])
    var _Year = SELECTEDVALUE('Table'[Year])
    Return
    DIVIDE(
        CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[SALESPERSON]=_Salesperson&&'Table'[Year]=_Year)),
        CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[SALESPERSON]=_Salesperson))
    )
     

    Hope this will help.

    Thank you.

9 Replies

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    hello Oros 

     

    please check if this accomodate your need.

     

    create a new measure with following DAX.

    Measure = 
    var _Salesperson = SELECTEDVALUE('Table'[SALESPERSON])
    var _Year = SELECTEDVALUE('Table'[Year])
    Return
    DIVIDE(
        CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[SALESPERSON]=_Salesperson&&'Table'[Year]=_Year)),
        CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[SALESPERSON]=_Salesperson))
    )
     

    Hope this will help.

    Thank you.

    • Oros's avatar
      Oros
      Post Prodigy
      1 year ago

      Hi Irwan,

       

      Your solution also works!  Thanks for your help!

    • Oros's avatar
      Oros
      Post Prodigy
      1 year ago

      Hello Irwan,

       

      Thank you for your quick reply.  What if I have a separate calendar table.  What should be the exact measure to use?  Thanks again.

       

      • Irwan's avatar
        Irwan
        Super User
        1 year ago

        hello Oros 

         

        since this is measure, you can change a little bit in _Year.

        Measure =
        var _Salesperson = SELECTEDVALUE('Table'[SALESPERSON])
        var _Year = SELECTEDVALUE('Table (2)'[Year])
        Return
        DIVIDE(
            CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[SALESPERSON]=_Salesperson&&'Table'[Year]=_Year)),
            CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[SALESPERSON]=_Salesperson))
        )
        Here is the result, show the same result even though it takes value from different table ('Table (2)').

        the slicer is also taking value from the Date table.

         

        Hope this will help.

        Thank you.

    • Oros's avatar
      Oros
      Post Prodigy
      1 year ago

      Hi Ashish_Mathur ,

       

      Thank you for your reply.  Your solution works, except that it always calculates at 100% any salesperson selection.  Any ideas?  Thanks again.

       

      (You solution works.  ONLY when all salespersons are selected, the % becomes 100%)

      (applying your solution to my context only works and shows 100% when all salespersons are selected)

      BUT when only a salesperson is selected, the 100% still shows up, instead of the particular percentage for the selected salesperson. If I would select the second salesperson only, the Sales and ALL Sales columns become equal, and therefore becomes 100%.  The $43,116.98 (all sales) is not showing up anymore.

       



      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        1 year ago

        Very confused about what you want.  What s wrong with my solution?