Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

IF Function Should not Apply to Column Total

I have a matrix table on the report page Column A: Overwork is ≥ 10h Column B: Overwork is < 10h   I used this formula for A: IF([Measure 1]>=10,[Measure 1],BLANK()) And I used this one for B:...
dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
5 years ago

Hi Anonymous -

 

Try this type of pattern

 

MeasureA =
IF (
    HASONEVALUE ( 'Table (2)'[Column1] ),
    IF ( SUM ( 'Table (2)'[Column1] ) > 10, SUM ( 'Table (2)'[Column1] ), BLANK () ),
    SUMX (
        FILTER ( ALLSELECTED ( 'Table (2)' ), 'Table (2)'[Column1] > 10 ),
        'Table (2)'[Column1]
    )
)

 

MeasureB =
IF (
    HASONEVALUE ( 'Table (2)'[Column1] ),
    IF (
        SUM ( 'Table (2)'[Column1] ) <= 10,
        SUM ( 'Table (2)'[Column1] ),
        BLANK ()
    ),
    SUMX (
        FILTER ( ALLSELECTED ( 'Table (2)' ), 'Table (2)'[Column1] <= 10 ),
        'Table (2)'[Column1]
    )
)

 

Hope this helps

David

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hm, not sure if this can work this way.

     

    There are quite complex formulas behind the measures. First, I calculate the total work hours, then I calculate the target hours per day, and finally I substract the latter from the first.

     

    Is there no other easy way where I can directly use the measures, instead of referencing to table[column]?
     
    Total Hours Workweek =
    CALCULATE (
    SUM ( Arbeitszeiten[Duration] ),
    FILTER (
    Arbeitszeiten,
    Arbeitszeiten[Zeitartnummer] = 1
    || Arbeitszeiten[Zeitartnummer] = 2
    || Arbeitszeiten[Zeitartnummer] = 4
    || Arbeitszeiten[Zeitartnummer] = 52
    || Arbeitszeiten[Zeitartnummer] = 20
    || Arbeitszeiten[Zeitartnummer] = 21
    || Arbeitszeiten[Zeitartnummer] = 30
    || Arbeitszeiten[Zeitartnummer] = 31
    || Arbeitszeiten[Zeitartnummer] = 50
    ),
    'Date'[WD] <> 6
    && 'Date'[WD] <> 7
    ) * 24
    + CALCULATE (
    SUM ( Arbeitszeiten[ARP Zeit] ),
    'Date'[WD] <> 6
    && 'Date'[WD] <> 7
    ) / 60 + [A08 Holiday]
     
    Target Hours =
    SUMX (
    ADDCOLUMNS (
    SUMMARIZE ( Arbeitszeiten, Arbeitszeiten[Datum] ),
    "MAX Soll",
    IF (
    [A08 Holiday] = BLANK (),
    CALCULATE ( MAX ( Arbeitszeiten[Tagesvorgabe] ) ),
    [A08 Holiday]
    )
    ),
    [MAX Soll]
    )
    • dedelman_clng's avatar
      dedelman_clng
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Hi Anonymous  - it depends on what is creating the "filter" on the table/matrix display. You haven't given much detail on the data or your model, or what the entire visual should look like (a table with nothing but measures is going to have one row). If you can provide more detail we might be able to help better.

       

      See How-to-Get-Your-Question-Answered-Quickly for tips on what to share with your question.

       

      David