I need a measure that can do the following circumstances:
1 - First I need to calculate the average monthly sales per user as shown in the following picture. Dividing the total revenue for n months that the specified user had revenue. Except that I need that also considering supplier ID.
- Revenue is the sum aggregation for the REVENUE column.
2 - I need the same calculation as the previous circumstance but now only considering supplier (an AllExcept should do this part).
3 - The final result should be as shown in the following picture:
The "USER_RESULT" would be the result of the n.1 circumstance, and the "SUPLIER_RESULT" would be the n.2.
I know that by using AVERAGE and SUMMARY one can probably do this very easily, but I haven't found a way to solve this problem yet.
How can I create a measure to display the final result?