Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Remove rows based on condition

Hi, I need to calculate the average unit selling price (AUSP) based on the condition below. Sample pbix here. If the total revenue of last year = 0 or total revenue of current year = 0, keep the ro...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous , 

    Please modify this formula ：Current Year.

     

    Current year =
VAR result1_ =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Append Table' ),
            'Append Table'[next_count] = BLANK ()
                && 'Append Table'[last_count] = BLANK ()
                && [min_] = 3
                && 'Append Table'[Fis] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
        ),
        [Revenue Current Year]
    )
RETURN
    result1_

     

     

    Best Regards

    Community Support Team _ Polly

     

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

Hi @Username,

I have try the formula. It works well. Please ensure your expected output.

 

Create a measure.

Measure = IF(ISBLANK([Revenue Current Year])||ISBLANK([Revenue Last Year])||[Revenue Current Year]=1||[Revenue Last Year]=1,1,BLANK())

The filter the measure.

 

 

If I have misunderstood your meaning, please provide contact me with your desired output.

 

Best Regards

Community Support Team _ Polly

 

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous , thank you so much for your help on this but that was not my expected output.

I would like to remove all rows which is not highlighted in yellow. You may see those highlighted rows/contracts, they have blank/zero total revenue either in FY2021 or FY2022. I want to calculate the average unit selling price (AUSP) in the top table by removing those not highlighted rows. The expected output is displayed in red box.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous , 

    Please try to see if it helps you.

    Create measures.

    Measure 3=
SUMX (
    FILTER (
        ALLSELECTED ( 'Append Table' ),
        'Append Table'[Fis] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
            && 'Append Table'[Contract Number]
                = SELECTEDVALUE ( 'Append Table'[Contract Number] )
    ),
    [Revenue Current Year]
)

    Measure 2 = IF(ISBLANK([Measure 3]),1,BLANK())

    Then filter the data.

    Then Create measures.

    min_ =
CALCULATE (
    DISTINCTCOUNT ( 'Append Table'[YearMonth] ),
    FILTER (
        ALLSELECTED ( 'Append Table' ),
        'Append Table'[Contract Number]
            = SELECTEDVALUE ( 'Append Table'[Contract Number] )
            && 'Append Table'[Fis] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
    )
)

    next_count =
CALCULATE (
    DISTINCTCOUNT ( 'Append Table'[YearMonth] ),
    FILTER (
        ALLSELECTED ( 'Append Table' ),
        'Append Table'[Contract Number]
            = SELECTEDVALUE ( 'Append Table'[Contract Number] )
            && RIGHT ( 'Append Table'[Fis], 4 ) + 0
                = RIGHT ( SELECTEDVALUE ( 'Calendar'[Fiscal Year] ), 4 ) + 1
    )
)

    last_count =
CALCULATE (
    DISTINCTCOUNT ( 'Append Table'[YearMonth] ),
    FILTER (
        ALLSELECTED ( 'Append Table' ),
        'Append Table'[Contract Number]
            = SELECTEDVALUE ( 'Append Table'[Contract Number] )
            && RIGHT ( 'Append Table'[Fis], 4 ) - 0
                = RIGHT ( SELECTEDVALUE ( 'Calendar'[Fiscal Year] ), 4 ) - 1
    )
)

    Current year =
VAR result1_ =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Append Table' ),
            [min_] = 3
                && 'Append Table'[next_count] = BLANK ()
                && 'Append Table'[Fis] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
        ),
        [Revenue Current Year]
    )
VAR result2_ =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Append Table' ),
            'Append Table'[next_count] = BLANK ()
                && 'Append Table'[last_count] = 3
                && 'Append Table'[Fis] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
        ),
        [Revenue Current Year]
    )
RETURN
    IF ( ISBLANK ( result2_ ), result1_, result2_ )

    If I have misunderstood your meaning, please provide contact me with your desired output.

     

    Best Regards

    Community Support Team _ Polly

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous , Thank you so much for the solution. The table below seems correct but the "Current year" measure for FY2022 is incorrect. After removing the unecessary rows, the revenue for FY2021 = 12097 is correct but FY2022 "Current year" figure should show up as 12315. 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

        If you have solve your problem, please share your way and mark your answer as the answer so it will be easier to search and be able to help more people.

         

        Best Regards

        Community Support Team _ Polly

         

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