Obsidian | Level 7

Calculations on measure by category within same column of data

I'm wondering if there is anyway to do a calculation by specific groups within the same column of data.

If I have the following data set, I want to be able to sum the Count column by Status then divide the sum of sold status count by the sum of proposed status count. Is this possible is VA?

Status      Count

Proposed      1

Sold              1

Proposed      1

Sold              1

Proposed      1

Proposed      1

I know I can manipulate the data prior to bring it into VA and columns for 'Sold Count' and 'Proposed Count' and would be able to do the calculation that way, just curious if there is another way.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Rhodochrosite | Level 12

Re: Calculations on measure by category within same column of data

I think you need to create two calculated items one for each status and then create a calculation between them

Var 1: if status = 'sold' then count else . | Aggregation = count.

Var 2: if status = 'proposed' then count else . | Aggregation = count.

Var 3: var 1 / var 2

I have not seen any example (yet) on how to do it using just one column 🙂

//Fredrik

2 REPLIES 2
Rhodochrosite | Level 12

Re: Calculations on measure by category within same column of data

I think you need to create two calculated items one for each status and then create a calculation between them

Var 1: if status = 'sold' then count else . | Aggregation = count.

Var 2: if status = 'proposed' then count else . | Aggregation = count.

Var 3: var 1 / var 2

I have not seen any example (yet) on how to do it using just one column 🙂

//Fredrik

Obsidian | Level 7

Re: Calculations on measure by category within same column of data

Ah yes, that works. Didn't think to do the counts in VA. Perfect!

Just for reference, here is the actual text that is working for me:

IF ( 'STATUS'n In ('SOLD') )
RETURN 'COUNT'n
ELSE 0

Discussion stats
• 2 replies
• 806 views
• 0 likes
• 2 in conversation