Margin for Selected Category

[Anonymous]

Hello,

I am attempting to create a table that will produce the margin % for a specific category. I have been attempting to use the following code:

(Sum({<[Distribution Channel] = {'Parts'}>}if([Total Revenue]<100 and [Total Revenue]>1,[Total Revenue],''))
-
Sum({<[Distribution Channel] = {'Parts'}>}if([Total Revenue]<100 and [Total Revenue]>1,[Total Cost],'')))
/
(Sum({<[Distribution Channel] = {'Parts'}>}if([Total Revenue]<100 and [Total Revenue]>1,[Total Revenue],'')))

Currently, this only outputs 100%. I believe that the cost portion is coming in as 0 because the if statement is dependent on the "Total Revenue" instead of the "Total Cost". Any thoughts on how to remedy the issue? Is there a way to pull the category in the code before I perform the sum function?

Thanks,

Dave

[Anonymous]

Try

if([Total Revenue]<100 and [Total Revenue]>1,

(Sum({<[Distribution Channel] = {'Parts'}>},[Total Revenue])
-
Sum({<[Distribution Channel] = {'Parts'}>}[Total Cost]))
/
(Sum({<[Distribution Channel] = {'Parts'}>}[Total Revenue])),'')

[Anonymous]

Thanks for the response. That version of the code pulls no data (displays "No data to display" in my chart). Thoughts?

[Anonymous]

Hard to say without seeing your data.  Maybe add an aggregation?

if(sum([Total Revenue])<100 and sum([Total Revenue])>1,

(Sum({<[Distribution Channel] = {'Parts'}>},[Total Revenue])
-
Sum({<[Distribution Channel] = {'Parts'}>}[Total Cost]))
/
(Sum({<[Distribution Channel] = {'Parts'}>}[Total Revenue])),'')

[Anonymous]

I tried this...

num(if(Sum({<[Distribution Channel] = {'Parts'}>}[Total Revenue])>1 and Sum({<[Distribution Channel] = {'Parts'}>}[Total Revenue])<100,

(Sum({<[Distribution Channel] = {'Parts'}>}[Total Revenue])
-
Sum({<[Distribution Channel] = {'Parts'}>}[Total Cost]))
/
Sum({<[Distribution Channel] = {'Parts'}>}[Total Revenue]),''),'#,##0%')

However, it just pulls one month that does not have the total cost and revenue completed (month hasn't closed yet). Thoughts?

[Anonymous]

Can you share a sample app?

[Anonymous]

What is that. Also, how would I go about it?