Cumulative percentage total based on negative or positive values of a measure

[Curious_PBI]

Hi everyone, hope you're well,

I am pretty new in DAX and I need some help on one of my projects.

My formulas:

#Sales Allocation =
DIVIDE([#vs Target Total $], [#SumVsTargetTotal]) -- my cumulated sum

#vs Target Total $ =
[#Total Sales] - [#TargetTotal] -- my difference between target and sales

#SumVsTargetTotal = CALCULATE(([#Total Sales] - [#TargetTotal]),ALLSELECTED(Target_All)) -- my first attempt to have the total sum of the measure #TargetTotal to be able to divide and obtain a cumulated sum

#SumVsTargetTotal+ = IF([#vs Target Total $]>=0, [#vs Target Total $], 0) -- cumulated attempt of positive values

#SumVsTargetTotal- = IF([#vs Target Total $]<0, [#vs Target Total $], 0) -- cumulated attempt of negativevalues

#Sales Allocation2 = IF([#vs Target Total $]>=0,
DIVIDE([#vs Target Total $], CALCULATE(([#SumVsTargetTotal+]),ALLSELECTED(Target_All))),
DIVIDE([#vs Target Total $], CALCULATE(([#SumVsTargetTotal-]),ALLSELECTED(Target_All)))) -- second attempt to obtain the expected result

Error Message when I try to use a calculate function with a filter on the value of my measure #vs Target Total $ to be able to have a cumulated sum of my measure #Sales Allocation: A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

With the formula:

#Sales Allocation = CALCULATE(DIVIDE([#vs Target Total $], [#SumVsTargetTotal]), [#vs Target Total $])

Expected result: Have a cumulated sum of 100% for negative values of #vs Target Total $ and of 100% for positive values as well.

Observation:
- I can't use a sumx cause my values come from a measure and not a table column in this case.
- I can't use a calculate function with a filter

I tried many things over many days but I begin to think that's impossible to do, I hope I'm mistaken or I don't see another way around to be able to realize that. I read a lot of forum threads.

I join an excel example of a sample, but I can't share the real data sample.

Thank you for all your participation and answers.

Have a great day!

EDIT: apparently we can't attach an excel file here, do there is a capture of the file:

 

[Curious_PBI]

Hi @v-yinliw-msft,

I just tried but, if I'm not mistaken, it doesn't change the situation cause I'm not able to obtain my expected result:

---

@Curious_PBI wrote:

Expected result: Have a cumulated sum of 100% for negative values of #vs Target Total $ and of 100% for positive values as well.

With the formula: 

#Sales Allocation =
var _vsTarget = [#vs Target Total $]
var _SumVsTargetTotal = [#SumVsTargetTotal]
return
Calculate(DIVIDE(_vsTarget, _SumVsTargetTotal))

I didn't add a filter to the calculate function because of the error message when you try to use a filter that is already part of the calculation.

In order to realize my goal, I need to find a way to extract the total of the negative and positive values separately and then divide my difference between target and sales by that number. I tried creating two different measures to obtain the total by the sign of values (positive or negative) but It's not working.

Thanks anyway!

[v-yinliw-msft]

Hi @Curious_PBI ,

You can use var in place of the measure:

For example:

#Sales Allocation = 

var _# vs Target Total $ = [# vs Target Total $]

var _# SumVsTargetTotal = [#SumVsTargetTotal]

return

Calculate(...)

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.