Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I use a matrix visual to visualize a multi-level contribution margin system for different projects.
Example:
The measure for the recruitments ("Rekrutierungen") in row 3 is a simple:
To display the values for a certain project as in the example above I simply use the Filters pane and drag a filter for the current year and the selected project in the "Filters on this page" fields.
In the matrix example you can see the costs for recruitment in row 5 are calculated correctly by simply using the measure:
However the value in the total column is not correct for this specific, filtered project.
The value in the "Kosten pro Rekrutierung" row 4 is the total value for all projects for the entire year 2021 while row 5 obviously calculates the recruitment costs "correctly" by calculating 7 * 1,661 = 11,627.
Where do I have to change my filter context (in the "Kosten pro Rekrutierung" measure?) to display the right value for recruitment costs as of the sum of the row values for any specific project I filter in the filters pane?
Thank you so much for the help
Solved! Go to Solution.
Hi @deboec ,
Is that, the total value is incorrect ,but the Feb or Mar is right?
You can consider to use the function HASONEVALUE(<columnName>) to calculate the total.
Kosten pro Rekrutierung =
VAR KostenRekrutierung =
IF(
ISBLANK( [Rekrutierungen] ),
BLANK(),
CALCULATE(
SUMX(
FILTER(
ALL( Kostenstellen ),
Kostenstellen[Kostenstelle] = 3630
|| Kostenstellen[Kostenstelle] = 3640
|| Kostenstellen[Kostenstelle] = 7110
),
[Kosten]
)
)
)
VAR AnzahlRekrutierungen =
CALCULATE( [Rekrutierungen], ALL( Kostenstellen[Kostenstelle] ) )
VAR Result =
ABS( KostenRekrutierung / AnzahlRekrutierungen )
VAR _total = calculate(sum(),FILTER(ALL(), )) // depend on your table fields to calculate all values
RETURN
IF( HASONEVALUE( Date[month] ), Result, _total )
if you it does not work , please share your pbix file without sensitive data.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @deboec ,
Is that, the total value is incorrect ,but the Feb or Mar is right?
You can consider to use the function HASONEVALUE(<columnName>) to calculate the total.
Kosten pro Rekrutierung =
VAR KostenRekrutierung =
IF(
ISBLANK( [Rekrutierungen] ),
BLANK(),
CALCULATE(
SUMX(
FILTER(
ALL( Kostenstellen ),
Kostenstellen[Kostenstelle] = 3630
|| Kostenstellen[Kostenstelle] = 3640
|| Kostenstellen[Kostenstelle] = 7110
),
[Kosten]
)
)
)
VAR AnzahlRekrutierungen =
CALCULATE( [Rekrutierungen], ALL( Kostenstellen[Kostenstelle] ) )
VAR Result =
ABS( KostenRekrutierung / AnzahlRekrutierungen )
VAR _total = calculate(sum(),FILTER(ALL(), )) // depend on your table fields to calculate all values
RETURN
IF( HASONEVALUE( Date[month] ), Result, _total )
if you it does not work , please share your pbix file without sensitive data.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |