Calculation of all sold flowers in summer that are also sold in other seasons

[Pivvv]

I have an example salestable of flowers as shown below.
I'm trying to make a pivot table that shows all sales of flowers that are sold in summer, but that also shows the sales if these flowers are sold in other seasons as well.

The end result should be a pivot table that only shows the records with 1.000 sales.

My formula for the measure "SummerSales" is as follows:

SummerSales:=
CALCULATE(
SUM(Tbl_Sales[Sales]);
Tbl_Sales[Season]="Summer"
)

It already shows 4 of the 7 lines but the red ones are missing
How can i make this work?
I was trying to involve VALUES but I can't manage to make a VALUES formule for all Flowers with Season=Summer... Maybe this is not possible.

 

[Ozeroth]

There seem to be a few ways of doing this...here's one:

=
CALCULATE (
    SUM ( Tbl_Sales[Sales] ),
[B]    CALCULATETABLE ([/B]
[B]        VALUES ( Tbl_Sales[Flower] ),[/B]
[B]        ALLEXCEPT ( Tbl_Sales, Tbl_Sales[Flower] ),[/B]
[B]        Tbl_Sales[Season] = "Summer"[/B]
[B]    )[/B]
)

The logic is to keep the current filter on Flower, but additionally filter Flower to only those that have been sold in Summer at least once.

The CALCULATETABLE does this by:

1. Using ALLEXCEPT ( Tbl_Sales, Tbl_Sales[Flower] ) to remove all filters except keep existing filter on Flower
2. Restricting Season to Summer using Tbl_Sales[Season] = "Summer"
3. Then returning values of Flower remaining, which will only include Flower values in the current filter context which have ever sold in Summer.

 

[Pivvv]

Tnx! It works perfectly (as shown below). You also say there are a few ways of doing this. I am really curious what other ways there are because this a great way for me to understand the more complex calclate formulas. I often have this scenario and until now I always solved it by filtering the pivot table on season=summer, then select all the Flowers and filter on those, and then removing the filter on Season. But this technique is sometimes to heavy for our SSAS server/too large pivot tables. So I am really interested in all possible ways of solving this scenario, especially for learning purposes, this would help me a lot!

 

[Ozeroth]

Glad it works!

Here are a couple of others:

=
CALCULATE (
    SUM ( Tbl_Sales[Sales] ),
    CALCULATETABLE (
        VALUES ( Tbl_Sales[Flower] ),
        FILTER ( ALL ( Tbl_Sales ), Tbl_Sales[Season] = "Summer" )
    )
)

=
CALCULATE (
    SUM ( Tbl_Sales[Sales] ),
    FILTER (
        VALUES ( Tbl_Sales[Flower] ),
        CALCULATE (
            COUNTROWS ( Tbl_Sales ),
            ALLEXCEPT ( Tbl_Sales, Tbl_Sales[Flower] ),
            Tbl_Sales[Season] = "Summer"
        )
            > 0
    )
)

 

[scottsen]

Nice work Oz

I tend to sorta... build up my measures 1 step at a time, so my solution is either easier to understand, or harder... depending on how your brain works

Total Count:=COUNTROWS(Table1)
Count of Avail in Summer:=CALCULATE([Total Count], Table1[Season] = "Summer")
Total Sales:=SUM(Table1[Sales])
Sales of Avail in Summer:=CALCULATE([Total Sales], FILTER(VALUES(Table1[Flower]), [Count of Avail in Summer] > 0))

Which is very much like Oz's last measure.

 

[Pivvv]

Tnx a lot!! All tested and they all work, also yours scottsen. But the first one of Ozeroth is definitly the most performant which I definitly need as i'm running a 32-bit laptop with 4 GB's RAM :') and I'm not able to add more than 2 rowlabels in my pivot table. Even when importing only the necessary columns = 5 columns and 1 measure

I'm going to analyze these formula's in the weekend as I need to understand how they work to benefit more from it in the future.
I'm like a lonely knight in a 5.000 employee company right now :')