Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
@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
https://xxlbi.com/blog/new-dax-function-isinscope/
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |