Total count for dimension when different dimension selected
I have a table as follows:
Now, I want to know the number of shops in the city. I use count(DISTINCT [Shop ID]). This works fine when City is the dimension:
But I want to filter on the shop_ID and I want this result:
But get either this:
I've tried total count, set analysis, even loaded another column in script (If isnull(shop_ID),'','1') and tried to calculate with sum instead of count, but at best get the last mentioned result. Could anyone please advice me how to proceed?
Re: Total count for dimension when different dimension selected
Hello and thanks for the reply,
yes, exactly. I want to count the number of shops in the same city as the location of shop in dimension line. The second expression works in dimension line, only the total count of shops is 11, but should be 5.