Hi all,
I'm back with another problem that I can't figure out how to do solve.
I've the table 'Billed' with the total invoiced in each division and month, and I want to add a column with the Total of C&R done per Division in each month (which I've in another table named 'C&R report'). I can't find a way to obtain the subtotal per division in each month, I tried different formulas but it doesn't work.
Below you can find the tables that I have:
C&R Report:
| Date | Division | C&R |
| 01-Jul | Retail | 1 |
| 01-Jul | Vehicles | 0 |
| 01-Jul | Ocean | 0 |
| 01-Jul | Air | 1 |
| 01-Jul | Air | 1 |
| 01-Jul | Ocean | 0 |
| 01-Jul | Ocean | 1 |
| 01-Jul | Retail | 0 |
| 01-Jul | Vehicles | 0 |
| 01-Jul | Retail | 0 |
| 01-Jul | Retail | 1 |
| 01-Jul | Vehicles | 1 |
| 01-Aug | Ocean | 0 |
| 01-Aug | Retail | 1 |
| 01-Aug | Ocean | 1 |
| 01-Aug | Vehicles | 1 |
| 01-Aug | Air | 0 |
| 01-Aug | Vehicles | 0 |
| 01-Aug | Retail | 1 |
| 01-Aug | Air | 1 |
| 01-Aug | Air | 0 |
| 01-Aug | Vehicles | 1 |
| 01-Aug | Air | 0 |
Billed:
| Date | Division | Total billed |
| 01-Jul | Retail | 200,199.00 |
| 01-Jul | Vehicles | 175,839.00 |
| 01-Jul | Ocean | 134,094.00 |
| 01-Jul | Air | 209,302.00 |
| 01-Aug | Retail | 210,290.00 |
| 01-Aug | Vehicles | 185,043.00 |
| 01-Aug | Ocean | 98,202.00 |
| 01-Aug | Air | 225,029.00 |
The result that I'm looking for would be as follow:
| Date | Division | Total billed | C&R per Division |
| 01-Jul | Retail | 200,199.00 | 2 |
| 01-Jul | Vehicles | 175,839.00 | 1 |
| 01-Jul | Ocean | 134,094.00 | 1 |
| 01-Jul | Air | 209,302.00 | 2 |
| 01-Aug | Retail | 210,290.00 | 2 |
| 01-Aug | Vehicles | 185,043.00 | 2 |
| 01-Aug | Ocean | 98,202.00 | 1 |
| 01-Aug | Air | 225,029.00 | 1 |
As you can see, the column "C&R per division" got the Total of C&R done in each month for each division.
How can I obtain that with DAX?
I tried the following:
CALCULATE(SUM('C&R Report'[To Be Reported in ET Metrics]),'C&R Report'[Division],ALLEXCEPT('C&R Ratio per Division','C&R Ratio per Division'[Date]))
But I get the total of C&R of the whole report.
Thanks for your help! Let me know if you need anything else.
