Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
deboec
Helper I
Helper I

Filter context for matrix visual total

Hi,

 

I use a matrix visual to visualize a multi-level contribution margin system for different projects.

Example:

deboec_0-1636376828945.png

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

 

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
v-chenwuz-msft
Community Support
Community Support

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.