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.
I have a list of people that receive 1 letter monthly. People are added to the list, so now, if I look back - I want to understand and craft the following based on people and count of letters received in past 32 months.
Here is a mock list:
people | total # of letters |
pp1 | 32 |
pp2 | 32 |
pp3 | 16 |
pp4 | 16 |
pp5 | 28 |
pp6 | 27 |
pp7 | 28 |
pp8 | 27 |
pp9 | 16 |
pp10 | 12 |
pp11 | 12 |
pp12 | 12 |
pp13 | 4 |
pp14 | 4 |
pp15 | 4 |
pp16 | 4 |
The goal is to create a bar chart and each total #, report the number of people that received that many letters in past 32 months, then on top of that, add how many replied to the letter that was sent (%)
Not sure how to craft this in a formula.
Solved! Go to Solution.
Hi !
I would rather prefer to solve this problem through Data Modeling. You have ResponseTble and Group1 table, both are connected through [email] column. You can put a slicer on your report from Group1 table to filter selected Groups. This will apply filter based on selection and only those emails will be checked in ResponseTable where the Group1 [email] criteria is matched. And then second filter will apply through DAX to check [Sent] = "Yes"
Hope this will help you get resolved the issue.
Regards,
Hasham
the table has each user individual, the date, and Yes or No if sent, and yes or no if responses, then yes or no if account updated. After importing the table into PBI, I then just pulled up a matrix, and have the people in column, then in values - count of Sent (filtered on sent value as yes), so shows me the count of sent based on the filter of the date as well in the filter section.
Hi !
You can get started by using following 2 measures;
Letter Sent = CALCULATE( COUNT(YourTable[People]), FILTER(YourTable, YourTable[Sent] = "Yes"))
Letter Recieved = CALCULATE( COUNT(YourTable[People]), FILTER(YourTable, YourTable[Responses] = "Yes"))
You can replace the YourTable with table name, we consider [People] as column name in your model, this is the column representing each individual, or you can use the Date column as well. We consider Sent & Reponses are 2 columns in your model to pick Sent = "Yes" to get No. of Letter sent count and Repsonses = "Yes" to get No. Of Letter recieved count.
Regards,
Hasham
Is there a way to filter the calculation based on a subset - example, i have 2 tables, the data that holds the people and response information, and the more people detailed information - both connected by peopleEmail. I further have a subset, a list of people names, details, and emails - i want to calculate just only for those, so as long as they match are in the response information table. Hope that makes sense. I keep getting filter expression error due to true/false statement. My thoughts are this - (it failed - but a start i guess)
Letters Sent Group1 = CALCULATE( COUNT(ResponseTble[email],
FILTER(ResponseTble, 'ResponseTble'[Sent] = "Yes"),
FILTER(ResponseTble, 'ResponseTble'[email] in
VALUES('Group1'[email])
)
Hi !
I would rather prefer to solve this problem through Data Modeling. You have ResponseTble and Group1 table, both are connected through [email] column. You can put a slicer on your report from Group1 table to filter selected Groups. This will apply filter based on selection and only those emails will be checked in ResponseTable where the Group1 [email] criteria is matched. And then second filter will apply through DAX to check [Sent] = "Yes"
Hope this will help you get resolved the issue.
Regards,
Hasham
That worked!!! Thanks!!
Hi !
Please share how you are currently tracking No. of Letters delivered vs. Reply recieved in your Data model.
Regards,
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |