Problem with my summarize table when I filter on category

[Irache]

Hello, 

So I have this Summarize table that show the number of IN and OUT per week :

Week	IN	OUT
2008-05	517	-4
2008-06	2	-153
2008-07	4	-107

This table is create with this measure :

Entrée/Sortie = 
VAR lastDataWeek =
    MAX ( Data[#Semaine de mesure] )
RETURN
    SUMMARIZE (
        ALL('Date_IN_OUT 1'),
        'Date_IN_OUT 1'[Semaine Entreprise],
        "Entrée", CALCULATE(SUM(Data[#Nombre entrée]),
                                FILTER(ALLSELECTED(Data),
                                Data[#Semaine entrée entreprise] = 'Date_IN_OUT 1'[Semaine Entreprise] && Data[#Semaine de mesure]==lastDataWeek)),
        "Sortie",CALCULATE(SUM(Data[#Nombre sortie]),
 
        FILTER(ALLSELECTED(Data),Data[#Semaine sortie entreprise] = 'Date_IN_OUT 1'[Semaine Entreprise] && Data[#Semaine de mesure] == lastDataWeek))
                    
            )

And it's work well until I want to make a filter on a shop that's the result :

Week	Shop	Entrée	Sortie
2008-05	Mag 1	517	-4
2008-05	Mag 2	517	-4
2008-05	Mag 3	517	-4
2008-05	Mag 4	517	-4
2008-05	Mag 5	517	-4
2008-05	Mag 6	517	-4

That's the mesure :

Entrée/Sortie = 
VAR lastDataWeek =
    MAX ( Data[#Semaine de mesure] )
RETURN
    SUMMARIZE (
        ALL('Data'),
        'Date_IN_OUT 1'[Semaine Entreprise],
        Data[#Nom complet magasin],
        "Entrée", CALCULATE(SUM(Data[#Nombre entrée]),
                                FILTER(ALLSELECTED(Data),
                                Data[#Semaine entrée entreprise] = 'Date_IN_OUT 1'[Semaine Entreprise] && Data[#Semaine de mesure]==lastDataWeek)),
        "Sortie",CALCULATE(SUM(Data[#Nombre sortie]),
 
        FILTER(ALLSELECTED(Data),Data[#Semaine sortie entreprise] = 'Date_IN_OUT 1'[Semaine Entreprise] && Data[#Semaine de mesure] == lastDataWeek))
            )

My fact table is "Data" and my date Table is "Date_IN_OUT".
It's been a long time that I'm blocked on this someone can help me please.

[tamerj1]

Hi @Irache 

please try

Entrée/Sortie =
VAR T =
ALL ( 'Data' )
RETURN
GENERATE (
SUMMARIZE ( T, 'Date_IN_OUT 1'[Semaine Entreprise], Data[#Nom complet magasin] ),
VAR lastDataWeek =
CALCULATE (
MAX ( Data[#Semaine de mesure] ),
ALL ( Data[#Nom complet magasin] )
)
VAR Entree =
CALCULATE (
SUM ( Data[#Nombre entrée] ),
FILTER (
T,
Data[#Semaine entree entreprise] = 'Date_IN_OUT 1'[Semaine Entreprise]
&& Data[#Semaine de mesure] == lastDataWeek
)
)
VAR Sortie =
CALCULATE (
SUM ( Data[#Nombre sortie] ),
FILTER (
T,
Data[#Semaine sortie entreprise] = 'Date_IN_OUT 1'[Semaine Entreprise]
&& Data[#Semaine de mesure] == lastDataWeek
)
)
RETURN
ROW ( "Entrée", Entree, "Sortie", Sortie )
)

[johnt75]

Try

Entrée/Sortie =
VAR lastDataWeek =
    MAX ( Data[#Semaine de mesure] )
RETURN
    ADDCOLUMNS (
        SUMMARIZE (
            'Data',
            'Date_IN_OUT 1'[Semaine Entreprise],
            Data[#Nom complet magasin]
        ),
        "Entrée",
            VAR Entreprise = 'Date_IN_OUT 1'[Semaine Entreprise]
            RETURN
                CALCULATE (
                    SUM ( Data[#Nombre entrée] ),
                    Data[#Semaine entrée entreprise] = Entreprise
                        && Data[#Semaine de mesure] == lastDataWeek
                ),
        "Sortie",
            VAR Entreprise = 'Date_IN_OUT 1'[Semaine Entreprise]
            RETURN
                CALCULATE (
                    SUM ( Data[#Nombre sortie] ),
                    Data[#Semaine sortie entreprise] = Entreprise
                        && Data[#Semaine de mesure] == lastDataWeek
                )
    )

[Irache]

Thanks for your response.
It's work for IN column but not for OUT column:

[johnt75]

Are you sure that there is OUT data for the other shops? The -4 which it put into the first shop matches the figure for the total number of outs which was produced by your previous code

[Irache]

For this case yes but for the week 6 of year 2008 this is not the good result we have 0 out :

Whereas in other data without filter they have -153 OUT :

[johnt75]

Again, that could be split across different shops. I don't see any differences between the code for the ins and for the outs, other than filtering on the in and out column from the Data table.

I would check the source data to see which shops had the outs in a given week and then compare that to the summarized table.

[Irache]

Hello,
For it to work well, il's necessary that it's appear -153 for 2008-06 week because this week is represented by one shop and this isn't the case.

[Imad-R]

I couldn't send the data.