group data by dimension (aggr function in qlik sense)

[edvapran]

Hi, there's no way to say this problem easily..

I have 3 tables:

• POS current month data table (item, location code, datetime, sales amount)
• plan table (location code, latitude,longitude, sales plan)
• location table (location code, latitude,longitude) 

These tables have a relation by location code column: PLAN 1-* POS *-1 LOCATION

Main goal is to show a map chart that displays location code on the map with it's sales plan execution percentage.

In Qlik Sense its super easy, there is a aggr function, that automatically groups by any dimmension:

sum([Sales amount]) / sum(aggr(sum([Sales plan]),[Location code]))

In powerBI Plan table I have added:

(new column) TotalSales = sumx(relatedtable(POS);POS[Sales amount])

(new measue) Execution = sum(Plan[TotalSales]) / sum(Plan[Sales plan])

Abviously now every location shows the same execution percentage, because there is no grouping by location. How to solve this?

[Greg_Deckler]

Generally add Location to your Legend and you will get your grouping.

[edvapran]

Does not allow to add this field to Map's Legend.

I have also tried to create a simple chart, same result, all locations show the same execution percentage.

[edvapran · Accepted solution]

Ok, I managed to find a solution, it has to with relationship, [Cross filter direction] has to be set to both directions. Have no idea why isn't this set as default...

[Greg_Deckler]

Bi-directional cross filtering used to be the default but they changed this some time back. Probably had to do with performance and that bi-directional cross filtering is often not necessary. It is more the exception than the rule.