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,
My data looks something like this:
CaseID Date Type Channel
1 1-4-2018 Complaint type A Phone
2 13-4-2018 Signal Mail
3 15-4-2018 Request Mail
4 18-4-2018 Complaint type B Other
Ofcourse the data is spread out over 7 years with 300.000+ rows. I have added columns which state what day each date is. What I want to do is as follows: I want to calculate the average number of cases per day per period which is selected in the slicer. So if for example 2018 is selected in the slicer, I only want the average number of cases per day in 2018. If 2018 and Q1 is selected in the slicer, I only want the average number of cases per day in Q1 of 2018, and so on. However I can't seem the get the right measure for this. Can anyone help me out with this?
Kind regards
Solved! Go to Solution.
Hi @Elsie14
As you said, i am confuzed about “I have added columns which state what day each date is”, what are these columns and how they effect the expected average?
Based on my understanding, " the average number of cases per day per period which is selected in the slicer" means :
sum of cases during the selected period/total numbers of the period
If it is in this case,
Create a date table which is related to your fact data table. In date table, add period columns like year, month, quarter, and count the number of days of each period.
count days per quarter = CALCULATE ( COUNT ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[YEAR], 'Table'[QUARTER] ) )
Create measures like below:
Count case ID = CALCULATE ( COUNTROWS ( Table2 ), ALLSELECTED ( 'Table2' ) )
daybetween = MAX('Table'[count days per quarter])
average = CALCULATE(DIVIDE([Count case ID],[daybetween]),ALLSELECTED('Table2'))
As a result, we will see
Note: make [CaseID] not blank in the Visual level filter, for we use the number of rows shown in the table to count for the sum total of cases.
Here is my pbix
Best Regards
Maggie
Hi @Elsie14
As you said, i am confuzed about “I have added columns which state what day each date is”, what are these columns and how they effect the expected average?
Based on my understanding, " the average number of cases per day per period which is selected in the slicer" means :
sum of cases during the selected period/total numbers of the period
If it is in this case,
Create a date table which is related to your fact data table. In date table, add period columns like year, month, quarter, and count the number of days of each period.
count days per quarter = CALCULATE ( COUNT ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[YEAR], 'Table'[QUARTER] ) )
Create measures like below:
Count case ID = CALCULATE ( COUNTROWS ( Table2 ), ALLSELECTED ( 'Table2' ) )
daybetween = MAX('Table'[count days per quarter])
average = CALCULATE(DIVIDE([Count case ID],[daybetween]),ALLSELECTED('Table2'))
As a result, we will see
Note: make [CaseID] not blank in the Visual level filter, for we use the number of rows shown in the table to count for the sum total of cases.
Here is my pbix
Best Regards
Maggie
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |