Forum Discussion
DIVIDE for just for specific codes
I have a dimensional table that shows me for each product the product group:
| Product | Product Group |
| A1 | A |
| A2 | A |
| B1 | B |
Then I have a sales table, that shows me the sales per Product and the last table with the budget per Product.
Now I would like to divide each sale on the product level versus the budget, but somehow the aggregation on the department level doesn't work.
| Department | Product | Sales | Budget | DIVIDE |
| A | A1 | 500 | 500 | 100% |
| A | A2 | 500 | 500 | 100% |
| A | A3 | 500 | 0% | |
| B | B1 | 250 | 300 | 83% |
| Total | 1750 | 1300 | 135% | |
| Right | 1250 | 1300 | 96% |
There should be 96% as a result shown but I got 135%.
Do I need an iterative function here? Like a SUMX?
1 Reply
joshua1990 , Use this for sales
sumx(summarize(table, Table[Department], Table[Product], "_1", sum(Table[sales]), "_2", sum(Table[budget])), if(isblank([_2]), Blank(), [_1]))
or use this using isinscope or hasonevalue in GT
