Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
this is probably very simple but have not found it online or atleast the searches I have are not right.
I have a sales target of lets say 100 and and within that 100 are categories - example cars 50 and bikes 50
I get a percentage out of 100 which is fine but when I filter down to categories the dax below will still use 100 as the denominator. I want to be able to filter to cars or bikes and the sales amount to be calculated against the target number for that category
any idea on what I can put in to enable me to filter to different categories
Solved! Go to Solution.
@Jitmondo , try like
Target % = calculate(Divide('salestable'[salessum], calculate('targettable'[targetsum], allselected() ) ) )
or
Target % = calculate(Divide('salestable'[salessum], calculate('targettable'[targetsum], all() ) ) )
Hi @Jitmondo
You can try this, create the measure below
Target % =
VAR _category =
SELECTEDVALUE ( Slicer[categories] )
VAR _salessum =
CALCULATE (
SUM ( salestable[sales] ),
FILTER ( ALL ( salestable ), salestable[categories] = _category )
)
VAR _targetsum =
CALCULATE (
SUM ( targettable[target] ),
FILTER ( ALL ( targettable ), targettable[categories] = _category )
)
RETURN
IF (
ISBLANK ( _category ),
'salestable'[salessum] / 'targettable'[targetsum],
_salessum / _targetsum
)
result
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.
Hi @Jitmondo
You can try this, create the measure below
Target % =
VAR _category =
SELECTEDVALUE ( Slicer[categories] )
VAR _salessum =
CALCULATE (
SUM ( salestable[sales] ),
FILTER ( ALL ( salestable ), salestable[categories] = _category )
)
VAR _targetsum =
CALCULATE (
SUM ( targettable[target] ),
FILTER ( ALL ( targettable ), targettable[categories] = _category )
)
RETURN
IF (
ISBLANK ( _category ),
'salestable'[salessum] / 'targettable'[targetsum],
_salessum / _targetsum
)
result
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.
@Jitmondo , try like
Target % = calculate(Divide('salestable'[salessum], calculate('targettable'[targetsum], allselected() ) ) )
or
Target % = calculate(Divide('salestable'[salessum], calculate('targettable'[targetsum], all() ) ) )