Exclude rows from measure

[chulpanvl]

Hello everyone!

I have a table of structure

row	month	contract	parameter	value
1	01.01.2021	N	A	100
2	01.02.2021	N	A	100
3	01.03.2021	N	A	100
4	01.04.2021	N	A	100
5	01.01.2021	N	B	20
6	01.02.2021	N	B	30
7	01.03.2021	N	B	0

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!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

 

[v-stephen-msft · Accepted solution]

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]

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] ) )