So, I'll try to be clear, and sorry if bad explained, I'm quite new on power pivot.
The data base is :
Table1
Another tabel is in relationship by product :
Table2
And I need to build a performance KPI in my excel dashboard to measure the theoritical output rate (from the second table) versus the real output rate (calculated by product form the firs table : total quantity (by product/workorder)) / total hours (by product quantity).
I already builded some measures :
Total Quantity:=SUM(Table1[Quantity])
Total Hour:=SUM(Table1[Hour])
And I use them correctly with Cubevalue in excel.
But now I struggle to make this calculation (performance result), done previously by the SUM of colomn B and D here below, by only one calculation (either by cubevalue or power pivot) :
performance = 22/22,9=104%
Thank you for your kind help
The data base is :
Table1
| Workorder | product | data_type | hour | quantity | others... (line, date, etc...) |
| 123456 | AA | A | 10 | xxx | |
| 123456 | AA | B | 2000 | xxx | |
| 123456 | AA | C | 1 | xxx | |
| 123457 | BB | A | 12 | xxx | |
| 123457 | BB | B | 3600 | xxx |
Another tabel is in relationship by product :
Table2
| Product | OutputRate |
| AA | 220 |
| BB | 260 |
And I need to build a performance KPI in my excel dashboard to measure the theoritical output rate (from the second table) versus the real output rate (calculated by product form the firs table : total quantity (by product/workorder)) / total hours (by product quantity).
I already builded some measures :
Total Quantity:=SUM(Table1[Quantity])
Total Hour:=SUM(Table1[Hour])
And I use them correctly with Cubevalue in excel.
But now I struggle to make this calculation (performance result), done previously by the SUM of colomn B and D here below, by only one calculation (either by cubevalue or power pivot) :
| Product | Hour | Quantity | Hour "target" (=quantity/OutputRate) |
| AA | 10 | 2000 | =2000/220=9,1 |
| BB | 12 | 3600 | =3600/260=13.8 |
| TOTAL : | 22 | 22,9 |
Thank you for your kind help
