Pivot color background depending on the avg value of dimension level

[Sebastian_Dec]

Hi, how to color the background depending on the dimension level?

For example, I have dimensions in a pivot table:
GT Gessica -> nazwa dostawcy -> Nazwa Sklep -> Artykuł.
And measures value like TMP.

My background color expression is:

If(Dimensionality()=1 and (sum(TMP))<(avg(total TMP)),RGB(255,156,156),
If(Dimensionality()=2 and (sum(TMP))<(avg(TMP)),RGB(178,29,29)
))		

I would like the average TMP value to be calculated for a given dimension.

For example:
dimension GT Gessica have avg TMP 18.30%, I have value 16.49% that is less than avg TMP and is in red (Its work because is calculate Total values).
dimension Nazwa dostawcy for GT Gessica: 11 SPOŻYWCZE SŁONE have avg TMP 16.49%, I have value 0.29% for 10055 CHABER that is less than avg TMP for this dimension and should be in red <- this not working, I don't know how to approach this

[vinieme12 · Accepted solution]

you need to specify the exact dimension field name to scope the total on as below

avg(total TMP) // will give overall total

avg(total <Field1> TMP) // will give overall total at Field1 level  including the angle bracket

avg(total <Field1,Field2> TMP) // will give overall total aggregated based on combination of Field1 & Field 2

 including the angle bracket

avg(total TMP)  // will give overall total

avg(total <Field1> TMP)  // will give overall total at Field1 level

avg(total <Field1,Field2> TMP)  // will give overall total aggregated based on combination of Field1 & Field 2

[Sebastian_Dec]

Wow, thank you, it works!

Now I need to use colors, but the initial code to show the values looks like this:

=
If(Dimensionality()=1, (avg(total obrot_netto_prom)/avg(total obrot_netto_total)),
If(Dimensionality()=2, (avg(total <hid_GT> obrot_netto_prom)/avg(total <hid_GT> obrot_netto_total)),
If(Dimensionality()=3, (avg(total <DostawcaId> obrot_netto_prom)/avg(total <DostawcaId> obrot_netto_total)),
If(Dimensionality()=4, (avg(total <skrot_sklep> obrot_netto_prom)/avg(total <skrot_sklep> obrot_netto_total)),
))))

[vinieme12]

try below

=
If(Dimensionality()=1, (avg(obrot_netto_prom)/avg(total obrot_netto_total)),
If(Dimensionality()=2, (avg( obrot_netto_prom)/avg( total <hid_GT> obrot_netto_total)),
If(Dimensionality()=3, (avg( obrot_netto_prom)/avg( total <hid_GT,DostawcaId> obrot_netto_total)),
If(Dimensionality()=4, (avg( obrot_netto_prom)/avg(total total <hid_GT,DostawcaId,skrot_sklep> obrot_netto_total)),
))))

[Sebastian_Dec]

Yes, its changed.
like You wrote:

---

@vinieme12 wrote: avg(total <Field1> TMP) // <one field> will give overall total at Field1 level including the angle bracket

avg(total <Field1,Field2> TMP) // <multi filed> will give overall total aggregated based on combination of Field1 & Field 2

---

At this moment I don't know if I want to use one field or many fields.

It's good that this is possible, but can You give some examples of when one field is used and when many fields are used?

The table is intended to show which stores/suppliers in a given category lag behind the average.

[vinieme12]

can you post some sample data?

[Sebastian_Dec]

Yes, please.
2 department (hid_GT), 3 suppliers (DostawcaId), 3 stores (skrot_sklep) and articles (Product).

TMP = obrot_netto_prom/obrot_netto_total

[vinieme12]

as below?

Dimension:

DostawcaId

skrot_sklep

Product

Measures:

=Avg(obrot_netto_prom)

BackgroundExpression

=if(
Avg(obrot_netto_prom)
<
Pick(Dimensionality()
,Avg(Total obrot_netto_total)
,Avg(Total <$(=GetObjectDimension(0))> obrot_netto_total)
,Avg(Total <$(=GetObjectDimension(0)),$(=GetObjectDimension(1))> obrot_netto_total)
)
,RGB(255,156,156)
,RGB(178,29,29))

Avg(obrot_netto_total)

Avg(total <$(=GetObjectDimension(0))> obrot_netto_total)

Avg(total <$(=GetObjectDimension(0)),$(=GetObjectDimension(1))> obrot_netto_total)

Dimensionality()

[Sebastian_Dec]

I have some problem with BackgroundExpression:

=if(
Avg(obrot_netto_prom)
<
Pick(Dimensionality()
,Avg(Total obrot_netto_total)
,Avg(Total <$(=GetObjectDimension(0))> obrot_netto_total)
,Avg(Total <$(=GetObjectDimension(0)),$(=GetObjectDimension(1))> obrot_netto_total)
)
,RGB(255,156,156)
,RGB(178,29,29))

Error:

The Pick() function should be so long?