DAX question: calculation excluding certain data

[hidenseek9]

Hi Power BI Community!

I am facing a problem with Power BI on excluding certain data when using DAX calculation.

I think there are two ways to go about this and it would be fantastic, if I can get answers for both.

Objective:

Calculate current year revenue % in comparison to target revenue, but exclude revenue generated from bicycle in current year revenue calculation.

1. Exclude bicycle revenue from current year revenue calculation BEFORE calculating the %.

When I calculate sum value from Jan to Mar'17 on revenue, I use below DAX.

CY REV = Calculate(Sum([Revenue]),Parallelperiod(Date,0,month))

and I use timeline Power BI visuals to select Jan to Mar'17.

With above, I want to exclude revenue value if the category is in bicycle.

What DAX can I add to above calculation to exclude bicycle revenue?

2. Exclude bicycle revenue WHEN calculating the % in comparison to the target revenue.

When I calculate the %, I use below calculation.

Target % = CY REV/calculate(sum(Target REV), parallelperiod(Date,0,month))*100

Is there any way to exclude bicycle revenue from this calculation?

Appreciate your support!

Many thanks,

H

[mattbrice · Accepted solution]

Firt off, I don't understand why you are using PARALLELPERIOD the way you are.  It is supposed to shift the visible calendar dates forwards or backwards for a different month or year result.  So as is with "0" as part of shifting, don't see how it does anything.

So current year revenue should just be:

CY Rev = SUM ( Table[Revenue] )

and CY Rev no bicycle's is :

CY Rev no Bicycle = CALCULATE ( [CY Rev] , Table[Revenue Category] <> "Bicycle" )

then target Revenue:

Target Revenue = SUM ( Table[Target REV] )

then finally % is just:

Target % = DIVIDE ( [CY Rev no Bicycle], [Target Revenue] )

[hidenseek9]

@mattbrice

Thank you so much for your feedback and advice!

It worked out perfectly!

H