- Painters are working on different Bst-Nr. (sites)
- Sometimes the total costs for a site are higher than the invoice (site was a bad one)
- Customer wants to know:
○ Which site ended with a loss?
○ Who (painter) worked on that site?
○ The percentage (by hrs) of each painter who worked there.
○ Finally each worker's share in EURO.
- As you can see from the screenshot below I could fulfill the requirements, BUT
- when I filter by "Mitarbeiter" (painter) the result is not what I expected -> second screenshot
- The Mitarbeiter-related percentage is gone (and 100% are applied)
- Then I created another DAX-calculation for Total Hours per BST (site; Total Stunden BST)
- As you can see, the value needed for percentage calculation is correct, BUT all workers are showing up in the list, although without values.
I watched several videos (A. Ferrari, Curbal, Guy in a Cube…) to get a hang on this, but now on Sunday evening I’m done (and have to accept that I still have a long way to go).
I also finished Sam McKay’s course ‘Ultimate Beginners Guide to Dax’ and ‘Mastering DAX calculations’ (half way through)
I played around with ALL, ALLSELECTED, ALLEXCEPT, REMOVEFILTERS, KEEPFILTERS.
When I look at the problems discussed here, I’m a bit shy to come to you with this simple one…
but since I’m (still) unable to solve it any solution would be highly appreciated.
The Data Model looks like this:
The used measures are:
- Total Stunden =
SUM(BaustellenStd[Stunden])
- PCT2 =
DIVIDE(
[Total Stunden],
CALCULATE( [Total Stunden], REMOVEFILTERS ( BaustellenStd[Mitarbeiter] ) )
- € Anteil pro Mitarbeiter =
CALCULATE
([Total Ergebnis] * [% Std Mitarbeiter]
)
- % Std Mitarbeiter =
CALCULATE
([Total Stunden] / [Total Stunden pro Baustelle]
)
- Total Stunden pro Baustelle =
CALCULATE
([Total Stunden],
ALLSELECTED(BaustellenStd[Baustellen-Nr.], BaustellenStd[Mitarbeiter])
)
Total Stunden BST =
CALCULATE(
[Total Stunden] ,
REMOVEFILTERS(BaustellenStd[Mitarbeiter])
)
As said earlier, nothing complicated (following Sam’s advice: start simple, improve later.), but for me it is.
