Hello, relatively new to QuickSight so apologies if this is a basic question.
I am working with sales data across different regions. Each region has it’s own sales target for the year. To set this up in QuickSight I have uploaded a file with the region targets, as below, then joined this with my sales dataset. Therefore each sales line item has an associated target based on the region, as below.
In order to use the Target data in my analyses I have to use Average, however this means that I cannot have multiple regions selected in my visuals. Filtering on one region works, but not multiple regions, as below.
My question is (1) is this the best way to work with targets in QuickSight, and (2) how can I use targets and filter for multiple regions.
I am not sure if this is what you were trying to achieve but I came up with this
I just used calculated field target summed which was created by this statement:
max({FY Target},[Region])
then i shown totals and i got this result which u can see above
It is a table grouped by Region with EMEA excluded with values of Sale and Target_summed
Even better visual for this is KPI
Thank you this has worked, however I am trying to use this new calculated field (Target Summed) as a reference line in a line graph and getting this error “Please contact the QuickSight team to solve this issue”. I am trying to show a graph like this, with the target as a reference line
Any ideas? Thanks for your help!
I came up with something like this
as for the value I used run calculated field
runningSum(sum(Sale),[{Win date} ASC])
as for tar I used
max({FY Target},[Region])
I did a line chart edited its format added reference line for calculated field tar with sum aggregation and max calculation.
Unfortunatelly for this to work every month has to have at least one record in database for every region. I solved this issue by adding records like:
| Region |
Customer |
Sale |
Win date |
FY Target |
| Central Europe |
NULL |
0 |
Jan-2023 |
800 |
| EMEA other |
NULL |
0 |
Jan-2023 |
500 |
| UK |
NULL |
0 |
Feb-2023 |
1000 |
| EMEA other |
NULL |
0 |
Feb-2023 |
500 |
| UK |
NULL |
0 |
May-2023 |
1000 |
| Central Europe |
NULL |
0 |
May-2023 |
800 |
Only with EVERY used month having at least one sale (even with null value) this solution will work. So you have to make changes in data ;/
also you can add only the first two rows so the very first month has records for all regions and use runningMax:
runningMax(sum(max({FY Target},[Region])),[{Win date} ASC])
this has followin effect
Thanks for your assistance. I don’t have access to edit the data to append “blank” rows for the missing months, as I’m connecting to an existing dataset. Is there anything else I can do to get this working? Appreciate your help!
