Forum Discussion
DAX Formula Help Working Out Percentages
Hi
I have a calculation dilemma if someone can help.
I have a data grid with the following columns.
My report has a date filter called DistributionDate. The more dates I select, i.e. 02/11/19, 03/11/19 it increments the count of CountofDistDate.
What I need to work out is the % of Returns / Drops regardless of dates selected. If I drop into my view the DistributionDate field, everything works as it should. However, when I take the field out, it should work out the % but it isn’t.
Here is my formula: % = DIVIDE(SUM('View_1BBB'[Percentage]), COUNTA('View_1BBB'[RouteID]))
Without DistrubtionDate (note % is wrong because I am dividing Percentage by CounofDisDate):
DisplayedRouteID | Returns | TotalDrops | Percentage | CountOfDistDate | % |
LD001 | 44 | 120 | 111.1111 | 3 | 37.03704 |
LD002 | 76 | 125 | 131.4843 | 3 | 43.82811 |
LD003 | 59 | 99 | 176.8519 | 3 | 58.95062 |
LD004 | 89 | 175 | 203.3283 | 4 | 50.83209 |
With DistributionDate:
DisplayedRouteID | Returns | TotalDrops | DistributionDate | Percentage | CountOfDistDate | % |
LD001 | 24 | 42 | 01/11/2019 00:00 | 57.14286 | 1 | 57.14286 |
LD001 | 4 | 42 | 02/11/2019 00:00 | 9.52381 | 1 | 9.52381 |
LD001 | 16 | 36 | 03/11/2019 00:00 | 44.44444 | 1 | 44.44444 |
LD002 | 1 | 33 | 29/10/2019 00:00 | 3.030303 | 1 | 3.030303 |
LD002 | 1 | 33 | 30/10/2019 00:00 | 3.030303 | 1 | 3.030303 |
LD002 | 74 | 59 | 04/11/2019 00:00 | 125.4237 | 1 | 125.4237 |
LD003 | 24 | 36 | 30/10/2019 00:00 | 66.66667 | 1 | 66.66667 |
LD003 | 21 | 36 | 02/11/2019 00:00 | 58.33333 | 1 | 58.33333 |
LD003 | 14 | 27 | 03/11/2019 00:00 | 51.85185 | 1 | 51.85185 |
LD004 | 33 | 41 | 30/10/2019 00:00 | 80.4878 | 1 | 80.4878 |
LD004 | 30 | 41 | 01/11/2019 00:00 | 73.17073 | 1 | 73.17073 |
LD004 | 1 | 40 | 02/11/2019 00:00 | 2.5 | 1 | 2.5 |
LD004 | 25 | 53 | 04/11/2019 00:00 | 47.16981 | 1 | 47.16981 |
I want to show the grid without the DistributionDate column.
How do I get my formula to work? Please help.
Thanks.
