Filtered measure

[Oros]

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%

[Irwan · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Oros]

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]

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

[Oros]

Hi @Ashish_Mathur,

My bad...I overlooked at one of the table relationships.  As usual, thank you so much for your kind help. 🙂

[Ashish_Mathur]

You are welcome.

[Irwan · Accepted solution]

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]

Hi @Irwan,

Your solution also works!  Thanks for your help!

[Oros]

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]

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.