Remove rows based on condition

[PBI_newuser]

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 rows, else remove them.

For example,

• When Fiscal Year = FY2022 and Quarter = Q1 are selected, remove all rows which do not have the total revenue of Q1 FY2021 = 0 or total revenue of Q1 FY2022 = 0.
• When Fiscal Year = FY 2022 is selected, remove all rows which do not have the total revenue of FY2021 = 0 or total revenue of FY2022 = 0.

How to create a measure to calculate AUSP based on the condition above?

[Anonymous]

Hi @PBI_newuser ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

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]

Hi @Anonymous , I am sorry that I am not sure which part are you referring to for your question "Does that make sense?". 

But, I would like to have the values in yellow box to be the same as red box. Would it be possible to do so? 

Here is the sample.

[Anonymous · Accepted solution]

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]

Hi @Anonymous , how to fix the "Current year" and "Last year" measures if only one Fiscal Year is selected? The result for "Current year" and "Last year" changed when FY2020 is not selected. 

The ultimate goal is for user to select one Fiscal Year to see the current year and last year result. Here is the sample.

[Anonymous]

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]

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]

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]

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]

Hi @PBI_newuser ,

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.

[PBI_newuser]

Hi @Anonymous , I haven't solve it. I want to sum the revenue after removing those rows and show it in the top table. How to get it?

[amitchandak]

@PBI_newuser , With help date table and time intellignece you can measure like these for thise qtr and last year same qtr (-4) 

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))

You need a measure like 

=

if(isblank([QTD Sales]) || isblank([Last year same QTD Sales]) , 1,blank() )

or

if(coalesce([QTD Sales],0) =0 || coalesce([Last year same QTD Sales],0) =0  , 1, blank() )

[PBI_newuser]

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]

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.

[PBI_newuser]

Hi @v-xiaotang , thank you so much! I have posted a similar case with simplified condition. Is that possible to solve? If yes, then this is not needed. Thanks again!

https://community.powerbi.com/t5/Desktop/Remove-rows-based-on-condition/m-p/2329718#M841241

[v-xiaotang]

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]

Hi @v-xiaotang , I wanted to delete this post but there is no "Delete" selection. May I know how to delete it?

[v-xiaotang]

Hi @PBI_newuser 

I noticed that another similar post of yours has been resolved. This post will be merged with it later. Thanks for your contribution to improve Power BI.❤️

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]

Hi @amitchandak, i tried to create a measure and filter to 1 but it doesn't work. How to modify the measure?