I have created Parameter (NPS Breakdown) and show it just below Filters so that users can pick Promoters/Detractors/Passives. Then I have created Measure with CASE ... WHEN block:
CASE [NPS Breakdown]
WHEN "Promoter" THEN [Promoters]
WHEN "Passive" THEN [Passive]
WHEN "Detractor" THEN [Detractors]
END
It is working pretty well. However, I cannot quite combine more measures here, so this fails:
CASE [NPS Breakdown]
WHEN "Promoter" THEN [Promoters] + [Promoters 2021]
WHEN "Passive" THEN [Passive] + [Passive 2021]
WHEN "Detractor" THEN [Detractors] + [Detractor 2021]
END
I have tried to create a combined measure i.e. IFNULL([Promoters],[Promoters 2021]), but it does not show any data.
If I, however, create Measure Values with all metrics I want, then it is working fine and all of them are on the chart at the same time. I know that we cannot use Measure Values that way so I kind of lack more ideas on how to approach the problem.
I guess the main question is, how can I combine two measures in the combined field so that data from both measures is reflected on the chart.
Any help would be much appreciated.
EDIT: I have managed to resolve the issue with the following change:
CASE [NPS Breakdown]
WHEN "Promoter" THEN SUM([Promoters]) + SUM([Promoters 2021])
WHEN "Passive" THEN SUM([Passive]) + SUM([Passive 2021])
WHEN "Detractor" THEN SUM([Detractors]) + SUM([Detractor 2021])
END
Regardless, I will share some snapshots of the workbook for future reference, maybe someone else will come across a similar problem.
@Grzegorz Gancarczyk If you have the data for that specific row for the total and 2021, yes - the calculation will work. But if the 2021 is on another row you need to aggregate them, so Tableau will do it based on multiple rows.
