topic Remove rows based on condition in Desktop

Remove rows based on condition

Anonymous — Thu, 10 Feb 2022 03:28:04 GMT

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?

Re: Remove rows based on condition

Anonymous — Tue, 08 Feb 2022 05:50:05 GMT

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

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.
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.

Re: Remove rows based on condition

amitchandak — Thu, 10 Feb 2022 03:35:58 GMT

Anonymous , 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() )

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

Re: Remove rows based on condition

Anonymous — Thu, 10 Feb 2022 04:28:41 GMT

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

Re: Remove rows based on condition

v-xiaotang — Fri, 11 Feb 2022 09:30:45 GMT

Hi Anonymous

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.

Re: Remove rows based on condition

Anonymous — Fri, 11 Feb 2022 11:11:38 GMT

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

Re: Remove rows based on condition

Anonymous — Mon, 14 Feb 2022 09:16:28 GMT

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.

Re: Remove rows based on condition

Anonymous — Mon, 14 Feb 2022 10:31:36 GMT

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.

Re: Remove rows based on condition

Anonymous — Tue, 15 Feb 2022 09:14:25 GMT

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.

Re: Remove rows based on condition

Anonymous — Tue, 15 Feb 2022 23:57:25 GMT

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.

Re: Remove rows based on condition

Anonymous — Wed, 16 Feb 2022 01:13:08 GMT

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.

Re: Remove rows based on condition

Anonymous — Wed, 16 Feb 2022 02:51:25 GMT

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?

Re: Remove rows based on condition

Anonymous — Wed, 16 Feb 2022 04:09:40 GMT

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.

Re: Remove rows based on condition

Anonymous — Wed, 16 Feb 2022 06:40:24 GMT

Hi Anonymous ,

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.

Re: Remove rows based on condition

Anonymous — Wed, 16 Feb 2022 06:41:12 GMT

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.

Re: Remove rows based on condition

Anonymous — Wed, 16 Feb 2022 07:20:22 GMT

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.

Re: Remove rows based on condition

v-xiaotang — Wed, 23 Feb 2022 07:37:38 GMT

Hi Anonymous

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.

Re: Remove rows based on condition

Anonymous — Wed, 23 Feb 2022 09:19:48 GMT

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

Re: Remove rows based on condition

v-xiaotang — Mon, 28 Feb 2022 08:13:17 GMT

Hi Anonymous

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.