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?

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.

Remove rows based on condition | Microsoft Fabric Community

18 Replies

Anonymous
Not applicable
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.
Sample_Remove rows based on condition (1).pbix194 KB
- PBI_newuser
  Post Prodigy
  4 years ago
  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.
amitchandak
Super User
4 years ago
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
  Post Prodigy
  4 years ago
  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.
  - 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.
- PBI_newuser
  Post Prodigy
  4 years ago
  Hi amitchandak, i tried to create a measure and filter to 1 but it doesn't work. How to modify the measure?

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.

Sample_Remove rows based on condition (1).pbix3.3 MB

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

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.

Sample_Remove rows based on condition (1).pbix194 KB

Anonymous
Not applicable
4 years ago
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
  Post Prodigy
  4 years ago
  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.

Forum Discussion

Remove rows based on condition

18 Replies

Related content

Remove Records based on Date Condition

Remove column based on condition

How to hide/remove duplicates based on condition

Remove Duplicates based on Condition

How to remove rows based on conditions