Forum Discussion

chulpanvl's avatar
chulpanvl
Helper I
4 years ago
Solved

Exclude rows from measure

Hello everyone!

I have a table of structure

rowmonthcontractparametervalue
101.01.2021NA100
201.02.2021NA100
301.03.2021NA100
401.04.2021NA100
501.01.2021NB20
601.02.2021NB30
701.03.2021NB0

 

I have measures:

param a =  CALCULATE(sum(‘Table’[value]),  ‘Table’[parameter] = «A»)

param b =  CALCULATE(sum(‘Table’[value]),  ‘Table’[parameter] = «B»)

ratio = DIVIDE(Table[param a], Table[param b])

 

What I want is to exclude from calculation of ratio the rows where value for parameter = b is 0 - for both numerator and denominator. I know how to do that for param b measure, I just need to add filter.

But how to filter out rows with the same month+contract for param a?

So, in the end I would get the ratio = (row 1 + row 2 + row 4)/(row 5 + row 6) for the example above.

 

Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi chulpanvl ,

     

    Since the data also needs to take into account the contract and the occurrence of 0 on multiple different dates, I added some data to the original sample data.

    Sample data

     

    1.Create the measure. I created a virtual table in the measure to filter out the value of parameter A corresponding to the date when the parameter B is 0, grouped by contract.

    Ratio =
VAR _table1 =
    SELECTCOLUMNS (
        FILTER ( 'Table', [parameter] = "B" && [value] = 0 ),
        "month1", [month],
        "contract1", [contract]
    )
VAR _table2 =
    FILTER (
        CROSSJOIN ( 'Table', _table1 ),
        [month1] = [month]
            && [contract1] = [contract]
    )
VAR _unwantedvalue =
    SUMX ( FILTER ( _table2, [parameter] = "A" ), [value] )
RETURN
    DIVIDE (
        SUMX ( FILTER ( 'Table', [parameter] = "A" ), [value] ) - _unwantedvalue,
        SUMX ( FILTER ( 'Table', [parameter] = "B" ), [value] )
    )

     

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi chulpanvl ,

     

    Since the data also needs to take into account the contract and the occurrence of 0 on multiple different dates, I added some data to the original sample data.

    Sample data

     

    1.Create the measure. I created a virtual table in the measure to filter out the value of parameter A corresponding to the date when the parameter B is 0, grouped by contract.

    Ratio =
VAR _table1 =
    SELECTCOLUMNS (
        FILTER ( 'Table', [parameter] = "B" && [value] = 0 ),
        "month1", [month],
        "contract1", [contract]
    )
VAR _table2 =
    FILTER (
        CROSSJOIN ( 'Table', _table1 ),
        [month1] = [month]
            && [contract1] = [contract]
    )
VAR _unwantedvalue =
    SUMX ( FILTER ( _table2, [parameter] = "A" ), [value] )
RETURN
    DIVIDE (
        SUMX ( FILTER ( 'Table', [parameter] = "A" ), [value] ) - _unwantedvalue,
        SUMX ( FILTER ( 'Table', [parameter] = "B" ), [value] )
    )

     

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Expected ratio: =
    VAR filtertable_B_Zero =
    FILTER ( Data, Data[parameter] = "B" && CALCULATE ( SUM ( Data[value] ) ) = 0 )
    VAR extract_month =
    MAXX ( filtertable_B_Zero, Data[month] )
    VAR param_a =
    FILTER ( Data, Data[parameter] = "A" && Data[month] <> extract_month )
    VAR param_b =
    FILTER ( Data, Data[parameter] = "B" && Data[month] <> extract_month )
    RETURN
    DIVIDE ( SUMX ( param_a, Data[value] ), SUMX ( param_b, Data[value] ) )