Fix Table Values?

[#1]

Hi all,

I got a specific problem with a table. As you can see in the picture below I got a table with some values on the dimension "Zeitzone". The values are all correct. The problem occurs, when I make a selection of the dimension (

The problem occurs, when I make a selection of the dimension (see below)

The expression in the table for the "Abweichung" is this: =(Avg([Netto-Plan-ZZ])-(Sum([n/n/n])/Sum(TOTAL [n/n/n])))*100

The problem is the red marked part. The value should only change, when I select a year, but not when I select any other dimension. Is it possible to do this?

Or is there another way to fix the values in the table that it shows "0,2" for "13:00-17:00" and not "-85,3".

I hope I made my problem clear to you and be glad for any help! If there are any questions, please ask.

Thx

Daniel

[#2]

Hi,

It would have been better had you attached the app. From what i understood you can try set analysis using this --

Avg({<zeitzone=,year={$(=only(year)-1}>}[Netto-Plan-ZZ]).. Ididnt write the full expression as i am not getting the logic .

[sivarajs]

The problem is you are using total function, whenever you are selecting some dim it will show the same value for all

try this

Avg({$<Zeitzone>}[Netto-Plan-ZZ])-(Sum([n/n/n])/Sum(TOTAL [n/n/n])))*100 ------it will exclude the selection

[CELAMBARASAN]

Hi,

Check something like this

=(Avg([Netto-Plan-ZZ])-(Sum([n/n/n])/Sum(TOTAL {1} [n/n/n])))*100

Celambarasan

[Jason_Michaelides · Accepted solution]

The TOTAL qualifier ignores the current row (dimension value) but is still based on the available dimension values, so if you select a dimension value (in this case 13:00-17:00) then you are limiting the available dimension values in the chart and TOTAL will adjust accordingly. You need to also exclude the dimension selection.

Instead of TOTAL, try {<Zeitzone=>} TOTAL giving:

=(Avg([Netto-Plan-ZZ])-(Sum([n/n/n])/Sum({<Zeitzone=>}TOTAL [n/n/n])))*100

Hope this helps,

Jason

[#6]

That was what I was looking for!

Excluding the dimension. Didn't think of this. Sometimes, it's that easy.

Thanks all of you for your help!