Forum Discussion
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
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
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] )