Forum Discussion
DAX for Sum and Average on category level
Hello Community,
I have below Sales metric data source in one database table:
| Metric Name | City Name | Target | Actual | Time Period |
| New Clients | New York | 25% | 15% | Jan-20 |
| New Clients | New York | 25% | 10% | Feb-20 |
| New Clients | New York | 25% | 10% | Mar-20 |
| New Clients | Boston | 25% | 20% | Jan-20 |
| New Clients | Boston | 25% | 30% | Feb-20 |
| New Clients | Boston | 25% | 35% | Mar-20 |
| Returning Clients | New York | 40% | 55% | Jan-20 |
| Returning Clients | New York | 40% | 35% | Feb-20 |
| Returning Clients | New York | 40% | 40% | Mar-20 |
| Returning Clients | Boston | 40% | 35% | Jan-20 |
| Returning Clients | Boston | 40% | 25% | Feb-20 |
| Returning Clients | Boston | 40% | 60% | Mar-20 |
I am trying to achieve the below result in Matrix Visual which shows totals for the rows but Average for the Metric Name category:
| Metric Name | Target | City Name | Jan-20 | Feb-20 | Mar-20 |
| New Clients | 25% | New York | 15% | 10% | 10% |
| Boston | 20% | 30% | 35% | ||
| Average | 18% | 20% | 23% | ||
| Returning Clients | 40% | New York | 55% | 35% | 40% |
| Boston | 35% | 25% | 60% | ||
| Average | 45% | 30% | 50% |
In matrix visual I find it challenging to have the first two columns and also having average as a sub-total but sum as a total for cities. Any guidance will be appreciated.
Thanks,
Jay
1 Reply
- Greg_Deckler
Community Champion
