In my data, I have multiple months, A subcategory, a commitment column, goal, actual, and a format column.
I want to replicate a table where all subcategories, commitment, actual, and goal are listed.
My goals and Actuals are just numbers, their formats are discussed in a separate column, so if the format column says it is M, then I consider my value in a million and if it says Percent, then i consider a % next to it.
In my requirement, I have a table where commitment is listed in a single column and in this case my goals and actuals will be a mix of formats, some values will be a currency, percent and some will be a number. so I used a formula to where I am using concat to help fix values. It works till the time i am only using one month of data in the date selection.
If all time range is selected, all months get listed in the table, and then if multiple months have Q1 value, then in the commitment column i see Question number 1 mentioned multiple times. I tried to average out the actual value so i can only see commitments listed once and then values next to it change according to month selection. But when I use Average aggregation in Actual column then My concat functions don't work and values are just flat INT values. so It becomes difficult to identify the format.
I tried to use this below formula, to fix the issue, by this I can solve the values and can use any date range and have commitment listed only once, but then i am not able to save the card, it gives error that unable to save card. Please help.
Case when format
='Percent' then CONCAT(ROUND((AVG(Actual
))*100,2),'%')
when format
='M' then CONCAT(ROUND((AVG(Actual
))/1000000,2),' ','M')
when format
='Mdollar' then CONCAT('$',ROUND((AVG(Actual
))/1000000,2),' ','M')
when format
='K' then CONCAT(ROUND((AVG(Actual
))/1000,2),' ','K')
when format
='#' then AVG(Actual
)*1
END
Attached is my sample data