Forum Discussion

deboec's avatar
deboec
Helper I
4 years ago
Solved

Filter context for matrix visual total

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:

Rekrutierungen = COUNT(Rekrutierungen_ab2020[Projekt]) of a table.
 
The measure for the costs per recruitment ("Kosten pro Rekrutierung") in row 4 is:
 
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 =
        KostenRekrutierung / AnzahlRekrutierungen
RETURN Abs(Result)
 

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:

Rekrutierungskosten = [Rekrutierungen] * [Kosten pro Rekrutierung]

 

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

 

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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. 

    How to use Hasonevalue() 

    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.

1 Reply

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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. 

    How to use Hasonevalue() 

    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.