Benchmarking Percentages in Chart Table with Set Analysis?

110 — Tue, 10 Sep 2024 10:10:37 GMT

So I have two measures and three dimensions

Dimensions -
Brand, ShopID , Week

Measures -

Sales , Cost

My first metric Cost Percent
Sum(Cost) / Sum (Sales)

My second metric is Benchmark is the average of Cost Percent by Week / Brand -
Avg(Aggr(Sum(Cost) / Sum(Sales), Brand,Week))

My third metric is to compare the difference between Cost Percent and Benchmark - which have been turned into master measures

mmCostPercent - mmBenchMark

This mostly works, however in a table the benchmark doesn't repeat over rows, ideally I'd like to ignore all filtering ( if user filtered on one brand / site etc ) in the benchmark field, other than date/brand.

Where I've used a set analysis with the master measure , it doesn't seem to apply .

i.e. mmCostPercent - ({<ShopID=>} mmBenchmark )

If I was doing this in SQL, I'd just pre-calculate all the numbers in a CTE and recombine, but I'd like to allow users to filter / see it live.

Any suggestions are most welcome, am hoping this is something fairly standard.

Edit - I've added a picture to show what I mean - I'd also like to be able to chart the 'vs Benchmark' metric over ShopID in a Time-Series line chart , to show the Shops relative performance to the benchmark over time.

Re: Benchmarking Percentages in Chart Table with Set Analysis?

brunobertels — Wed, 11 Sep 2024 07:54:48 GMT

maybe

Avg(Aggr(nodistinct Sum(Cost) / Sum(Sales), Brand,Week))

topic Benchmarking Percentages in Chart Table with Set Analysis? in App Development

Benchmarking Percentages in Chart Table with Set Analysis?

Re: Benchmarking Percentages in Chart Table with Set Analysis?