I have a list that looks like this:
etc
When I aggregate these to a year level, I want to create a "heads up", so if one category falls below 20% of the total sales, I want a 0 to appear, otherwise 1.
So in the output I want to have:
This is because for Category A for customer 1, they only reached 18% of the total sales for 2021.
Doing this with measures is fine and I can create the 0s and 1s, just as they should.
For example Score A = IF(Divide(SUM(SalesA),SUMS(SalesB))>0.2,0,1)
BUT, I want to know the total amounts of 1s and 0s, I have understood that there is no way to "summarize" measures, but what options are there available for me to use?
Assuming I have 100 customers and 5 categories, the maximum score I can have is 500, I want to know how many points I scored in one year and compare this to the 500 potential points.
| Customer | Sales Category A | Sales category B | Total sales | Date |
| 1 | 200 | 1000 | 1200 | 2021-01-01 |
| 1 | 100 | 1500 | 1600 | 2021-02-01 |
| 1 | 500 | 1000 | 1500 | 2021-03-01 |
| 2 | 200 | 300 | 500 | 2021-01-01 |
When I aggregate these to a year level, I want to create a "heads up", so if one category falls below 20% of the total sales, I want a 0 to appear, otherwise 1.
So in the output I want to have:
| Customer | Sales Score A | Sales Score B |
| 1 | 0 | 1 |
| 2 | 1 | 1 |
This is because for Category A for customer 1, they only reached 18% of the total sales for 2021.
Doing this with measures is fine and I can create the 0s and 1s, just as they should.
For example Score A = IF(Divide(SUM(SalesA),SUMS(SalesB))>0.2,0,1)
BUT, I want to know the total amounts of 1s and 0s, I have understood that there is no way to "summarize" measures, but what options are there available for me to use?
Assuming I have 100 customers and 5 categories, the maximum score I can have is 500, I want to know how many points I scored in one year and compare this to the 500 potential points.
