Using selected value as variable in set analysis

[Amit_B]

Hi,

I have the attached dataset for example (below).
Each row gets 2 year fields. The primary year is Year, and the secondary is YearCalc.
There is a KPI that summarizes the amount. When the user selects a particular YearCalc (for example - 2024), the KPI should get the sum of the amount in the rows where Year is 2024 (even if the YearCalc is different).

For example:
When selecting YearCalc=2024, the Total Amount is 1007.
One of the rows as YearCalc=2025.

I tried using a variable that get the selected value and use it in set analysis.
Like that: Sum({<Year={"=$(vSelectedYearCalc)"}>}Amount)
There should be additional filters, like Group field.
So, I think maybe the set analysis formula should be more complex.

Thanks!

The dataset:

Load * Inline [
Group, Year, Amount, Qty, YearCalc
A, 2022, 358, 67, 2023
A, 2023, 830, 134, 2023
A, 2024, 205, 15, 2024
B, 2022, 672, 87, 2022
B, 2023, 769, 186, 2024
B, 2024, 599, 96, 2025
C, 2022, 431, 78, 2023
C, 2023, 780, 159, 2024
C, 2024, 203, 65, 2024
];

[Chanty4u]

Create a variable 

LET vSelectedYearCalc = Only(YearCalc);

Your set expression like below 

Sum({<Year={"$(=vSelectedYearCalc)"}, Group=P(Group)>} Amount)

[TauseefKhan]

Hi @Amit_B,
You need to create a variable that dynamically captures the selected value of YearCalc and then use that variable in a set analysis expression to filter the Year field instead.

Variable to store the selected value of YearCalc:
vSelectedYearCalc = IF(GetSelectedCount(YearCalc)=1, GetFieldSelections(YearCalc), NULL())

//Use the variable in Set Analysis to filter based on the Year field:
Sum({<Year={"$(vSelectedYearCalc)"}>} Amount)

If you want to also filter based on selected groups, ensure you have a variable for selected groups:
vSelectedGroup = GetFieldSelections(Group)

// Then use the variable in the Set Analysis expression
Sum({<Year={"$(vSelectedYearCalc)"}, Group={"$(vSelectedGroup)"}>} Amount)

****Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.****

[marcus_sommer]

You don't need the variables else you could directly reference to the selections. Therefore try:

sum({< Year = p(YearCalc), YearCalc >} Amount)

blue = reference to the set selection against the field Year

green = ignore the selection in regard to Amount