Filtering a dimension in a table

[davehutchinson]

Hi, sorry, another newbie question here...

I have read through other peoples solutions to this on this forum but none of them are working in my sheet.

I have a KPI measure, that is filtered and summing up correctly.

I then need to show a list of suppliers with their sum individually.

So I need to filter the dimension in the table, but for some reason its not working.

The KPI formula that is working correctly is:

SUM({<[Over-Under Spent Flag per supplier]={"Over Spent"}>}[Over-Under Spent])

So the formula I've put on the dimension in  my table is;

IF({<[Over-Under Spent Flag per supplier] = 'Over Spent'>} [Supplier (MTP) 05 Sep'22])

But this isn't calculating.

Can anyone tell me where I've gone wrong?

[justISO]

Hi, I see you are mixing 2 things, set analysis and IF condition. Your KPI works fine, as you are summing [Over-Under Spent], but with set analysis defining to take only "Over Spent" cases. In table formula is incorrect, as if you want to use IF, it should be something like this:

IF([Over-Under Spent Flag per supplier] = 'Over Spent', [Supplier (MTP) 05 Sep'22])

You will probably see some NULL values, which you can 'hide' with going to table properties, add-ons->data handling and uncheck 'include zero values'.

[BrunPierre]

It should be as below

If([Over-Under Spent Flag per supplier]='Over Spent',[Supplier (MTP) 05 Sep'22])

Alternatively, with Set Analysis + Aggr()

Aggr(Only({$<[Over-Under Spent Flag per supplier]={'Over Spent'}>} [Supplier (MTP) 05 Sep'22]), [Supplier (MTP) 05 Sep'22])