Forum Discussion

PBI_newuser's avatar
PBI_newuser
Post Prodigy
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 PBI_newuser , 

    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 PBI_newuser ,

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.

  • PBI_newuser's avatar
    PBI_newuser
    Post Prodigy
    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 PBI_newuser , 

      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.

      • PBI_newuser's avatar
        PBI_newuser
        Post Prodigy
        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.