cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hidenseek9 Helper V
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.

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

1 ACCEPTED SOLUTION

Accepted Solutions
mattbrice Solution Sage
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] )

View solution in original post

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

View solution in original post

hidenseek9 Helper V
Helper V

Re: DAX question: calculation excluding certain data

@mattbrice

 

Thank you so much for your feedback and advice!

It worked out perfectly!

 

H

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘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