Add "National" to dataset that only has Regions

[Adfc_NZ]

Hi,

I have a set of transactional data that uses a dim table with the regions. All good here, nothing special.

Issue is, the customer wants a barchart with an "extra" bar representing the "national", i.e. the totals.

I need your help and advise on this, because as I can see it, I have 2 options:

1- Summarise the data in the load script and concatenate it back to the fact with the "Region" being "National"

A few pros and cons, the major con imo is the fact that I'd then need to exclude "National" from all other areas of the app where "National" is not needed

2- Create a Dimension with the list of the Regions + "National", then in a measure in the chart to display each region + the national, use "Pick(Match(" along with 1 Set Analysis per region (that actually exist in the dataset) plus another where "Region=" then sums the amount.

This works very well, but the major con is that because of the Set Analysis the Region filter no longer works on this visual.

I am inclined to go with option 2, but just wondered if anyone here has a different solution to this requirement which might be more "flexible" than the 2 above?

Thank you.

[Adfc_NZ]

I tried adding the following:

Let vRegionSelectedCount = GetSelectedCount(Region);

And then wrap the "Add Load" inside an if:

If $(vRegionSelectedCount)= 0 then
For J = 1 to P
Let vTotalSales = vTotalSales + HCValue(#hc1.measure.1, J);
Next

Add Load
'National' as Region, //Country is dimension Label.
$(vTotalSales) as 'Budget ($)' //Sales is measure Label.
Autogenerate 1;
end if

But this doesn't work:

What am I doing wrong? Or is this not even possible?

[hanna_choi]

Hi @Adfc_NZ 

I succeeded by changing the script as follows.

I have found that GetSelectedCount(Region) is not calculated.
So I set it as a variable to receive it as a constant value.

************

Let vRegionSelectedCount = $(vCountCountry);
Let P = HCNoRows();
If $(vRegionSelectedCount)= 0 then
For J = 1 to P
Let vRegionSelectedCount = vRegionSelectedCount + HCValue(#hc1.measure.1, J);
Next

Add Load
'National' as Country,
$(vRegionSelectedCount) as 'Budget ($)'
Autogenerate 1;
end if

**********************

***If I select Canada, the chart is display only canada and 'National' is disappear.

[Adfc_NZ]

Hi @hanna_choi,

Great idea, calculate the SelectedCount outside and then pass it into the script, love it! 🙂

Thank you for your time, this solution will work brilliantly.

And thanks also to everyone who contributed with ideas! 🙂