Distinct filter a column and summing a related column

[Anonymous]

Hi All,

Newbie here, just wondering if a measure is doable here. I want to filter distinct values in one column (in this case names) and sum the values of another column based on the distinct filter. Is this possible using a measure? (told you I was new...:))

Below is just a condensed list as an example. Certain people are required to visit an area a number of times per month. These are tracked in a separate column. I'm wanting to create a card that has a measure of the required visits vs the actual visits. Hope this makes sense. Thank you for any help provided.

Names          Visits required

George              7

George              7

Peter                  3

George              3

Mike                  3

Paul                   3

[Anonymous]

Thank you so much @PhilipTreacy !! Works like a charm. I owe you a coffee or something and cna only hope to pay it forward some day.

Sorry for not @ you in the first reply. Still learning. Thanks again 🙂

[PhilipTreacy]

No worries 🙂

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Please @ mention me or I'll miss your reply.  Type @ then select my name from the list.

Download sample PBIX

You can calculate the Total Visits Required using this measure

Total Visits Required = SUMX(SUMMARIZE('Table','Table'[Area],'Table'[Names],'Table'[Visits required]),'Table'[Visits required])

and then display that in a card

You've worked out the actual visits already?  Do you know how to get both values into a single card?  You can create another something measure like this

Total v Actual Visits = "Total Visits : " & [Total Visits Required] & " v Actual Visits :" & [Actual Visits]

There's an example in my PBIX file above.

Regards

Phil

[PhilipTreacy]

Hi @Anonymous 

Sounds doable.  What's the actual result thast you want look like?  The table above just shows Visist Requited.  Where are the number of actual visits?

A card will just show data for 1 person, but that person can be changed by a slicer.  Is that what you want?

Regards

Phil

[Anonymous]

Thanks for the quick reply!

I have a slicer which I can select the area. My data is read from a sharepoint list where a salesman eneters their visit to their assigned area. George is supposed to get 7 visits in a month and Steve 3 for a total of 10 for the west. My goal is to get the required visits in a card and the actual visits. The acutal visits I get by countrows. The total for the area is what I can't get my head wrapped around. Maybe I'm going about it the wrong way but the end result is select the west slicer and get the total vs actual. Hope this makes sense. Thx

Names          Visits required   Area          Date

George              7                     West         May 4

George              7                     West         May 17

Peter                  3                     East          May 13

Peter                  3                     East          May16

George              3                     East          May16

Mike                  3                     North       May 29

Paul                   3                     North       May29

Steve                 3                     West        May29