Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Please @ mention me or I'll miss your reply. Type @ then select my name from the list.
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
Proud to be a Super User!
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 🙂
No worries 🙂
Proud to be a Super User!
Please @ mention me or I'll miss your reply. Type @ then select my name from the list.
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
Proud to be a Super User!
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
Proud to be a Super User!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |