Calculate percentage of one value against another value in the same column

The calculation I want to do is for each month, calculate ColumnC(When ColumnB = ''H2")/ColumnC(When ColumnB = ''H1"). I need to create a calculated field. How can I do that?

Screen Shot 2022-11-23 at 4.17.42 PM

Not quite sure I follow exactly what you want to do. Sounds like you want to a calculation based on what’s in column B. Is ColumnC a percent already or does the percent need to be calculated? You’ll likely want something like I have below but I’ll need a little more info before I can help finish the calculated field:

{ColumnB} = H1, [calculation]

Hi, column C is not percent. ColumnC is a value, I want to calculate: the value (ColumnC) of H2/ the value (ColumnC) of H1 for each month.
the results should be
Screen Shot 2022-11-23 at 4.36.26 PM

I tried:
ifelse({columnB}='H2', {columnC}/ifelse({columnB}='H1', {columnC}, NULL), NULL)
it only gave me NULL values. The problem is H1 and H2 are in the same column but on different rows.

Hi @Bruton3, you might want to per-process this data in your source database to add a new column that will store the H2 values in the newly column but in the same row and then you create the % values accordingly.

Let us know if this solution approach works for you.



Here is the expression you can use for it

sum(ifelse(ColumnB=“H2”, ColumnC, 0), [Month])/sum(ifelse(ColumnB=“H1”, ColumnC, 0), [Month])


Hi @Bruton3. Did the response from @DeepakS or @Tatyana_Yakushev help you? Let us know if this is resolved. And if it is, please help the community by marking the answer as a “Solution”. Many thanks.

1 Like

Hi @Bruton3 - Hope the response from @DeepakS or @Tatyana_Yakushev were helpful.
I am marking the reply as “Solution,” but let us know if this is not resolved.
Thanks for posting your questions on the QuickSight CommunityQ&A Forum!