Sum by Category Measure in a visual with no Category

[Tom_G]

Hello 

Say I have a table with three columns - Shop Name, City Name and Fruit Sold.

Shop Name      City Name      Fruit Sold 

Fruit Galore         Antwerp          137

Fruit Fest             London          245

Fresh Fruit            London           567

Fruit n Veg           Paris                231

Fruit House          Antwerp          668

I want to display the sum of Fruit Sold per City Name in a measure within a visual that also has the Shop Name in it (but NOT the City Name). There is also no City Name slicer on the tab.

So the visual would look like

Shop Name    Fruit Sold

Fruit Galore    805

Fruit Fest       812

Fresh Fruit      812

Fruit n Veg      231

Fruit House     805

This is simple if City Name is included in the visual or is slicing the visual from outside.

But what I can't work out is how to have Fruit Sold by City Name in a measure WITHOUT City Name also being in the visual or slicing the visual from outside.

Any help much appreciated.

[Tom_G · Accepted solution]

Hi @amitchandak The below based on what you sent seems to be working, I will check in details and confirm.

Fruit Sold per City = AverageX(Values('Table'[City]),

calculate(sum('Table'[Fruit by Shop]),

ALLEXCEPT('Table,'Table'[City],'Table'[Shop Status])))

[sathya_s · Accepted solution]

Hi @Tom_G ,

The chief issue is that there is a row level filter on 'Shop Name'. Once we remove that, we would be able to aggregate based on 'City'. 

Give this a try!

FruitSoldMeasure = 
VAR city = SELECTEDVALUE(FruitSales[City Name])
RETURN CALCULATE(
                 SUM(FruitSales[Fruit Sold]), 
                 REMOVEFILTERS(FruitSales[Shop Name]), 
                 FruitSales[City Name] = city
                 )

[Tom_G]

Hi @sathya_s - thanks for the measure! It seems to work well. I notice there are slight changes in the behaviour of this measure vs the ALLEXCEPT one below, which relates to responsiveness of the measure to other slicers on the tab, which I didn't mention. ALLSELECTED is more responsive to those other slicers than ALLEXCEPT. I will check this in detail and let you know the result. Thanks again!

[sathya_s]

Sure @Tom_G 👍

[Tom_G]

Hi @sathya_s  I checked and in fact both the ALLSELECTED and ALLEXCEPT versions work for the problem specified. ALLSELECTED works better to have responsiveness to any other slicers on the tab, ALLEXCEPT works better if you don't want that responsiveness. So I marked both as solutions. Thanks to you and @amitchandak for the help.

[amitchandak]

@Tom_G , If that is sum, how is different from a visual having only shop name

If that avg it can make diff

averageX(Values(Table[City]), calculate(sum(Table[Fruit Sold])) )

[Tom_G · Accepted solution]

Hi @amitchandak The below based on what you sent seems to be working, I will check in details and confirm.

Fruit Sold per City = AverageX(Values('Table'[City]),

calculate(sum('Table'[Fruit by Shop]),

ALLEXCEPT('Table,'Table'[City],'Table'[Shop Status])))

[Tom_G]

Hi @amitchandak thanks for the response.

The measure you provided gives me the fruit sold per shop name, not the fruit sold per city on each shop line. 

This is the closest I have below, (as you can see there are a couple of other columns explained below that also feature). The below is not working correctly in some specific cases and i'm not sure why exactly. Maybe you know?

Fruit sold per city = VAR _CITY = MAX('Table'[City])

RETURN

CALCULATE(

SUM('Table'[Fruit by Shop]), /// this is a calculated column which is the sum of fruits per shop as in some cases the shops are split out into two or three lines ///

'Table'[City] = _CITY,'Table'[Shop Status] = "Open", /// a column that states whether a shop is open or not ///

REMOVEFILTERS('Table'[Shop Name])

)

Thanks