I have a program, and some programs have subprograms. I need to build a calculation that adjusts the KPI visual numbers accordingly. For example, the program “Del” has two subprograms, “Task1” and “Task2,” with a total budget of 1,264,000. When I select the program “Del,” the KPI should display 1,264,000. If I filter by subprogram and select “Task1,” it should display 1,234,000.
I was able to build a calculation for a table using this formula:
Since you are using level aware calculation, the kpi visual must be looking for calculation reference and throwing similar error. One workaround to this is creating a pivot table with all the related fields and then hiding these fields. Also, this workaround has a limitation of not being able to increase font size though. If you can share sample data in the Arena can try helping with a sample workaround.
However, I am curious to know whether the data granularity is different in the dataset. As I see you are using a level aware calculation with max at subprogram level, are there multiple records for each program and sub-program tuple? If not we may not need this level aware calculation.
Thank you for your response. I’ve attached a sample screenshot below that shows the data where I was able to generate the desired output in a pivot table, displaying the unique budget for each program and its subprogram using the “Test” field. However, when I try to use the “Test” field in the KPI, I encounter an error. I’m unsure how to achieve the same result for the KPI.
I modified the kpi visual to a pivot table to get similar KPI visual. But as mentioned earlier, the font size configuration is a challenge here.
However, since the reason to implement level aware calculation was not clear, I also configured another KPI visual with the budget field and same set of filters. Please check out which one suits your use case!
When filtering for the program “con_one,” the KPI should display the sum of its unique subprogram budgets, which is 810,000. If a specific subprogram of “con_one” is selected, the KPI should display only the budget for the selected subprogram.
I have republished the analysis. Let me brief what was the miss.
Since I am using pivot table, I have created a dummy string to pivot the values against and have collapsed all fields and then disabled the ± controls from the pivot.
In the previous version I missed collapsing all row fields, so the number continued to appear in 2 rows.
For the KPI visual, created a RANK of user id based on program - sub program to limit records and filtered users with rank 1. This will not work if user# to prog-subprog mapping has many to one relationship, like same user id reported against the program-sub program tuple.
It worked for my current scenario, but what if we have the same resources repeated in other programs? I have a scenario like this. How to build ranking for this as I don’t unique fields in the dataset.