Problem: sum variable for which a dimension has an occurence of 1

[MrBosch]

Hi All

I have been searching for a long time today for this (seemingly simple problem) but can't find it.

I have a field [Project code] with projectcodes. Sometimes a [Project code] has sub-projects (so the occurence is >1 in th frequency table).

Now I want to

1) chart the variable value [profit result %] on the Y-axis for the dimension [Project code] (so only the projects that have 1 occurence (so... are distinct and not a part of a 'group of projects'): where and how to add these conditions in a bar graph and

2) do some calculations with this [Project Code | where occurence = 1] selection, so I need proper set analysis example. What I can come up with is wrongly: =sum ( count(distinct([Project code] = '1'), [profit result])

Hope anyone can help me out here! Highly appreciated.

Stay healthy!

Alex

[Or]

Sounds like you'd do better to calculate a flag in your script for this and then use that later, something along the lines of:

Load [Project Code], Count([Project Code]) as [Project Count]

Resident YourTable

Group by [Project Code];

Your front-end formula would then be e.g.

Sum({< [Project Count]={1} >} [profit result])

[marcus_sommer · Accepted solution]

If there are no further dependencies and you need this logic more often the suggested flag-solution within the script should be most suitable. If not you may use something like this:

sum([profit result]) * -(count(distinct [Project code]) = 1) 

- Marcus

[MrBosch]

This is indeed the easiest solution. Thank you for that!

[MrBosch]

Hello Or,

Your solution might work but it has appeared too difficult for me to implement. I don't know the exact working of 'Resident YourTable' and grouping but perhaps it will benefit others. I appreciate your effort though.

Thanks.