topic Re: Select filtered AND unfiltered values in one column in DAX Commands and Tips

Select filtered AND unfiltered values in one column

CC2019 — Thu, 02 Jul 2020 19:54:32 GMT

Hello, Pundits!

I have one column with dates, one column with five distinct names (multiple rows of each name, though) and another column with the respective sales made for that particular name and date (again multiple rows). Basically, a day-by-day sales tracker made in Excel.

There are supposed to be just three salespersons on record, for billing purposes, though there is no limit on the number of people who can make sales over telephone.

What Im trying to do, is:

1. filter the sales for the three official salespersons into one column

2. filter the sales for the other two into another column

3. somehow distribute, evenly, the sales for the two unofficial salespersons, across the official three.

Ive been wrangling with sumx(filter combinations for a while now, and am reaching nowhere.

Searching across here and Google didnt really help, either, I understand the basics okay, i think, but maybe I'm dumber than I think I am.

Would appreciate any help anyone would be kind enough to send my way.

the closest I've come to getting anywhere is the below:

FilteredValues = IF(Sales[Sales]=0,0,SUMX(FILTER(Sales,OR(Sales[EmpId]=280,Sales[EmpId]=508)),Sales[Sales])

Re: Select filtered AND unfiltered values in one column

AllisonKennedy — Fri, 03 Jul 2020 04:43:21 GMT

You can use the || as an OR filter to get the three Sales people into the one FILTER expression (just replace the 209 with the ID you want):

IF(Sales[Sales]=0,0,SUMX(FILTER(Sales,Sales[EmpId]=280||Sales[EmpId]=508||Sales[EmpId]=209),Sales[Sales]))

Are you happy having the employee id hard coded into the measure?

Once you've got the part above working, you'll need to do something similar to add the remaining sales, but will probably need to use ALL or something else to clear filters on that part depending on what visual you want to use it in.

Re: Select filtered AND unfiltered values in one column

stevedep — Fri, 03 Jul 2020 05:42:21 GMT

Hi,

I have something for you which assumes this data model:

So you have a list of offical reps and unoffical reps.

When you drag in the list of offical reps, the sales of the unoffical reps will be equally added:

This works by using the characteristic that some sales is blank, which is the sales of the unoffical reps. its broken down into pieces here.:

NoOfficialRepSales = CALCULATE(SUM(Sales[Sales Amount]);ISBLANK('Official Rep'[Rep]))

NumberOfOfficalSalesReps = CALCULATE(COUNTX('Official Rep';SUM(Sales[Sales Amount])); ALLSELECTED('Official Rep'[Rep]))

AdditionalDistributedUnOfficialSales = DIVIDE([NoOfficialRepSales];[NumberOfOfficalSalesReps])

TotalSales = IF ( HASONEVALUE ( 'Official Rep'[Rep] ); IF ( NOT ( ISBLANK ( SELECTEDVALUE ( Sales[Rep] ) ) ); SUM ( Sales[Sales Amount] ) + [AdditionalDistributedUnOfficialSales]; BLANK () ); SUMX ( ALLSELECTED ( 'Official Rep'[Rep] ); CALCULATE ( SUM ( Sales[Sales Amount] ) ) ) )

Link to file here.

Hope it works for you, if so, pls mark as solution. Thumbs up for the effort is appreciated.

Kind regards, Steve.

p.s.

NumberOfOfficalSalesReps should be labeled: NumberOfUNOffialSalesRepsales

Re: Select filtered AND unfiltered values in one column

CC2019 — Fri, 03 Jul 2020 05:42:31 GMT

Hi Steve,

That worked a CHARM! Exactly what I had in mind, while wrangling with the problem.

Had the separate tables, couldn't quite get them to play together.

Your post just makes everything so very clear and simple.

Thank you, Sir!

Re: Select filtered AND unfiltered values in one column

stevedep — Fri, 03 Jul 2020 05:43:30 GMT

Happy to read and glad to be of help!

Re: Select filtered AND unfiltered values in one column

CC2019 — Fri, 03 Jul 2020 05:51:26 GMT

Hi Allison,

Thank you, that works. ALL helps with the remaining sales, too.

I did plug this in, and get the results I wanted, yet have decided to use Steve's solution as it is something new for me to learn and add to the knowledge base.

Wonderful community, this, always amazing people helping out. I'm in awe.