Create a dashboard with visuals showing totals from two different sets of data

Question & Answer
, , ,
1

QS Masters, I need some support with a problem that I have encountered.

I have a problem creating a data panel with QS. For me, it is a problem because it is the first time I will need to create this type of visual and fields with sums and subtractions, but for some it may be something common, so I will try to explain.
I need to create a panel with values ​​of sums from a table, this sum is the total of amounts requested as a budget for expenses in 2025.
The data is in a dataset, and this first table contains the predicted values ​​associated with the year 2025 and categories A, B and C.
In another dataset, there is a table with records of amounts spent in 2025 for categories A, B and C, and there may be more than one record for each category.
I need to create a panel containing a visual (KPI for example) showing the total predicted values ​​for each category and a visual with the predicted total minus the amounts already spent (second dataset).
Can anyone give me a tip, suggest study material, or give me guidance on how to set up this analysis?
I added a spreadsheet with two tabs simulating the two sets of data.

Thanks in advance.
(Translated with Google… =D)

ID Ano orçamento Categoria Valor
12024 2024 A R$ 50.000,00
22024 2024 B R$ 40.000,00
12025 2025 A R$ 70.000,00
22025 2025 B R$ 55.000,00
32025 2025 C R$ 25.000,00
ID Ano orçamento Valor Categoria Descrição
12025 2025 R$ 1.500,00 C GASTOS RECORRENTES_12025-C
22025 2025 R$ 500,00 B GASTOS RECORRENTES_22025-B
32025 2025 R$ 500,00 B GASTOS RECORRENTES_32025-B
42025 2025 R$ 250,00 B GASTOS RECORRENTES_42025-B
62025 2025 R$ 100,00 A GASTOS RECORRENTES_62025-A
72025 2025 R$ 2.000,00 C GASTOS RECORRENTES_72025-C
82025 2025 R$ 2.500,00 A GASTOS RECORRENTES_82025-A
92025 2025 R$ 200,00 A GASTOS RECORRENTES_92025-A
102025 2025 R$ 150,00 A GASTOS RECORRENTES_102025-A

DATASETS-TEST_A-B
DATASETS-TEST_A-B792×490 10.2 KB

3

Hi @Bilck ,

Don’t worry, we all had our first time!! The community is a great place to get help, so you have started well.

Understanding your use case, I can think of the following execution steps:
Step 1: create a dataset joining both the tables. Keeping the actuals as left table and goals as right table, we can join using year and category.
Step 2: Because category can have multiple entries, we need to consider window calculation to fetch yearly goals .
Sample calculation to do so would be : avgOver(goalvalue, [year, category],pre_agg)

Step 3: this new calculated field can be used in KPI to give goal value for each year and category.
You can likely do the rest calculation.

Also if you can upload a sample data and create a sample analysis in Arena, we can work on a sample solution.

Thanks,
Prantika

1 Like
4

Thanks a lot , i will look into this alternative and give it a try.

If I need to follow the instructions I will check how to create the test environment in Arena.
for now, thank you very much.

1 Like
5

Hi @Bilck,
It’s been awhile since we last heard from you, did you have any additional questions regarding your post or were you able to find a work around for your case?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

6

Obrigado pela mensagem, a princípio a solução indicada esta ok.
Agradeço o apoio.

1 Like