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.

PBI_newuser's avatar
PBI_newuser
Post Prodigy
4 years ago

Hi, there are some conditions I would exclude from the sum. Sample here. 

  1. If the current monthly revenue for same contract number is the same as last month, then exclude it from the sum. For example, Contract Number 880024048, the revenue for 2019-10, 2019-11 & 2019-12 is same as 2018-10, 2018-11 & 2018-12. The sum of revenue for FY20 should be 0 and revenue last year = 3658.
  2. If the contract start date of a contract number is the next day of a previous contract end date with the same product and same amount, exclude them from the sum. For example, Product B, contract number 880074407 has contract start date the next dat from contract end date of 880049783 and both contract have same amount, then exclude them from the calculation. Only sum the revenue if there is no revenue last year or the revenue last year is not the same as this year.

 

 

 

 

v-xiaotang's avatar
v-xiaotang
Community Support
4 years ago

Hi PBI_newuser 

I think this is a challenge to solve it, I'll need some time to think about it. Of course, others will help you also.

 

 

Best Regards,

Community Support Team _Tang

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

    • v-xiaotang's avatar
      v-xiaotang
      Community Support
      4 years ago

      Hi PBI_newuser 

      Thanks for your reply. We will check that link later. If this post is repeated or no longer needed for discussion, to help other users find posts more accurately, could you please delete it? Thanks

       

       

      Best Regards,

      Community Support Team _Tang

      If this post helps, 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 v-xiaotang , I wanted to delete this post but there is no "Delete" selection. May I know how to delete it?