Forum Discussion
Helper IVCalculating a divide for a table - what am I missing??
I have a table like below, that details cases & hrs for a large datatset. The intent here is to calculate cases per hr. The desired result is to be able to visualize CPH at 3 different levels: Cumulative CPH (if no slicer selected), Unit (when selected), Hub (when selected) and finally down to the building CPH. I have no problem getting the correct number at the row level, but once I remove slicers, the calc is no longer correct. It must be simple, but I'm having a total block.
| Unit | Hub | Bldg | Cases | Hrs |
| 1 | 1 | a | 77833 | 1396 |
| 1 | 1 | b | 75713 | 1121 |
| 1 | 1 | c | 420953 | 6081 |
| 1 | 2 | d | 20723 | 515 |
| 1 | 2 | e | 242874 | 2769 |
| 1 | 2 | f | 189318 | 3233 |
| 2 | 3 | g | 364389 | 3777 |
| 2 | 3 | h | 809959 | 10862 |
| 2 | 3 | i | 426925 | 6403 |
| 2 | 4 | j | 119439 | 425 |
| 2 | 4 | k | 138445 | 2190 |
| 2 | 4 | l | 504590 | 6522 |
Any help would be greatly appreciated!
This will work correctly at all levels,
Total Cases = SUM(Table[Cases])
Total Hours = SUM(Table[Hrs])
CPH = DIVIDE([Total Cases], [Total Hours])
Hi unknown917,
can you please clairfy better what you want to achieve, showing examples of the 3 levels you are defining?
1 - Cumulative with respect to what?
2 - Unit - what do you mean here? And here the CPH would still need to be cumulative?
3 - Hub - what do you mean here? And here the CPH would still need to be cumulative?
Please show images and clearly point out the desired results including in case slicer etc
Thanks
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
6 Replies
- MasonMA
Super User
This will work correctly at all levels,
Total Cases = SUM(Table[Cases])
Total Hours = SUM(Table[Hrs])
CPH = DIVIDE([Total Cases], [Total Hours])
unknown917The issue with this is that when I drill down to the Bldg level, the CPH target stays the same, when it should calc the CPH at the bldg level
- FBergamaschi
Can you please sow images abou the issue? Otherwise we cannot help more
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
- FBergamaschi
Hi unknown917,
can you please clairfy better what you want to achieve, showing examples of the 3 levels you are defining?
1 - Cumulative with respect to what?
2 - Unit - what do you mean here? And here the CPH would still need to be cumulative?
3 - Hub - what do you mean here? And here the CPH would still need to be cumulative?
Please show images and clearly point out the desired results including in case slicer etc
Thanks
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI