Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table where it displays date values from a slicer and their total amounts.
I used a measure to calculate the average of those totals between the date selected.
Instead it is giving me the average of the totals per day.
Accept average per Date | Date |
2076 | 09/01/2019 |
1541 | 09/02/2019 |
1802 | 09/03/2019 |
1536 | 09/04/2019 |
1348 | 09/05/2019 |
1929 | 09/07/2019 |
1559 | 09/08/2019 |
1952 | 09/09/2019 |
1421 | 09/10/2019 |
1685 | The average of totals |
What measure should i use to get this result:
Accept average per Date | Date |
2076 | 09/01/2019 |
1541 | 09/02/2019 |
1802 | 09/03/2019 |
1536 | 09/04/2019 |
1348 | 09/05/2019 |
1929 | 09/07/2019 |
1559 | 09/08/2019 |
1952 | 09/09/2019 |
1421 | 09/10/2019 |
1516 | The sum of totals per day divided by number days between selected dates. or average of total per days. |
Solved! Go to Solution.
Hi @ggzmorsh ,
If I understand your requirement correctly that you want to calculate the average for the selected date range.
If so, please refer to the measures below.
interval days = VAR maxday = MAX ( 'Table'[Date] ) VAR minday = MIN ( 'Table'[Date] ) RETURN DATEDIFF ( minday, maxday, DAY ) + 1 Average = VAR total = CALCULATE ( SUM ( 'Table'[Value] ), ALLSELECTED ( 'Table' ) ) RETURN DIVIDE ( total, [interval days] )
Here is the output.
More details, you also could refer to my attachement.
If you still need help, please share your desired output so that we could help further on it.
Best Regards,
Cherry
Hi @ggzmorsh ,
If I understand your requirement correctly that you want to calculate the average for the selected date range.
If so, please refer to the measures below.
interval days = VAR maxday = MAX ( 'Table'[Date] ) VAR minday = MIN ( 'Table'[Date] ) RETURN DATEDIFF ( minday, maxday, DAY ) + 1 Average = VAR total = CALCULATE ( SUM ( 'Table'[Value] ), ALLSELECTED ( 'Table' ) ) RETURN DIVIDE ( total, [interval days] )
Here is the output.
More details, you also could refer to my attachement.
If you still need help, please share your desired output so that we could help further on it.
Best Regards,
Cherry
Worked perfectly. Thanks!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |