Dynamic Dimension or Expression based on GetPossibleCount

[whiteymcaces]

I have a major App where I have a chart that I want analysis on Stock Items that are on Special.

Specials are created in the source data, sometimes there is a single item, and sometimes there are multiple items. When there are multiple items, usually they are the same type of item, but just different colours.

I want to create a chart that has 1 row per Special, per Region. There are 6 Regions. If there is only 1 Special, then the chart will only have 6 rows. If there are 2 Specials, then the chart will have 12 rows etc.

If the Special only has 1 Stock Item then I want the data in the cell under the Heading 'Barcode' to be the actual Barcode, however, if the Special has more than 1 Stock Item, then I want the data in the cell under the Heading "Barcode' to be hard coded to the word 'Various'.

If there are multiple Specials in the same chart, then the ones with 1 Stock Item should have the Barcode, and the ones with more than 1 Stock Item should have 'Various'. All in the same chart.

This can be a Calculated Dimension or and Expression - whichever works.

[sunny_talwar]

Something like this

Dimension

Region

Expressions

If(Count({<hasSpecial = {'1'}>}Barcode) > 1, 'Various', Barcode)

Sum({<hasSpecial = {'1'}>}Amount)

[whiteymcaces]

What happens with no selections? I need all Specials in the chart.

[sunny_talwar]

I see this without selections

Selection was made to show that if there is only one Barcode, it will show up as the Barcode number rather than Various

[whiteymcaces]

There should be up to 4 rows per Region, 1 for each Special. Maximum 24 Row, based on Sales. If the Special has only 1 Stock Item, it should show the Barcode. If the Special has more than 1 Stock Item it should show 'Various' but the Sales should be for all Stock Items within that Special.

[sunny_talwar]

Like this?

[whiteymcaces]

Yes, but I'm not doing a pivot table, and I don't want to see the Special Dimension.

[sunny_talwar · Accepted solution]

This?

[whiteymcaces]

That's looking good. What's the Expression?

[sunny_talwar]

Dimension

=Dual(Region, Aggr(Rank(TOTAL Special&Region), Region, Special))

Expression

If(Count({<hasSpecial = {'1'}>}Barcode) > 1, 'Various', Barcode)

Sum({<hasSpecial = {'1'}>}Amount)

I have also attached the sample qvw with my previous response