cancel
Showing results for
Did you mean:
Helper V

## DAX question: calculation excluding certain data

Hi Power BI Community!

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

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?

Many thanks,

H

1 ACCEPTED SOLUTION

Accepted Solutions
Solution Sage

## Re: DAX question: calculation excluding certain data

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] )`
2 REPLIES 2
Solution Sage

## Re: DAX question: calculation excluding certain data

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] )`
Helper V

## Re: DAX question: calculation excluding certain data

@mattbrice

It worked out perfectly!

H

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors