SUM by category

[Anonymous]

Hello,

I have a table and i want to create a mesure to calculate sum by each Cat, if i have the same Esp for each Cat and 2 dates i have to take only the mount of the lastdate

Cat	Esp	Mount	Date
A	1	54	05/01/2021
A	1	54	01/01/2021
A	2	54	01/01/2021
C	3	6	01/02/2021

The measure have to give me that :
For A = 54 (Esp=1 & Date=05/01/2021)+54 (Esp=2 & Date=01/01/2021)=>108

Cat	Esp	Mount	Sum (Measure)	Date
A	1	54	108	05/01/2021
A	1	54	108	01/01/2021
A	2	54	108	01/01/2021
C	3	6	6	01/02/2021

Thank for your help!

[tamerj1 · Accepted solution]

Hi @Anonymous 
Here is the sample file with the solution https://www.dropbox.com/t/GTSytgeJpOM8WoQO
One way to that is by starting with a calculated column that retrieves the last date as per your requirement.

Last Date = 
VAR CurrentCatEspTable =
    FILTER (
        Data,
        Data[Cat] = EARLIER ( Data[Cat] )
            && Data[Esp] = EARLIER ( Data[Esp] )
    )
VAR Result =
    MAXX ( CurrentCatEspTable, Data[Date] )
RETURN
    Result

Then we can create our measure

CatSUM = 
CALCULATE ( 
    SUM ( Data[Mount] ),
    REMOVEFILTERS(),
    VALUES ( Data[Cat] ),
    FILTER ( 
        ALL ( Data ),
        Data[Date] = Data[Last Date]
    )       
)

The report looks like this

Please let me know if this answers your query. Have a great day!

[Anonymous]

Hello,

this is the real calculated column :

test ano sim =

var y= CALCULATE(SUMX(ALL('/BIC/OHZOFOCA13'[NNI modificateur condition de paiement]),SUMX(DISTINCT('/BIC/OHZOFOCA13'[Numero commande]),FIRSTNONBLANK('/BIC/OHZOFOCA13'[Montant total de la commande],0))))

return IF( '/BIC/OHZOFOCA13'[MODIF_COND]="YF60 => Y30P" && TOTALYTD(y,'Période'[Période])>20000 || '/BIC/OHZOFOCA13'[MODIF_COND]="YF60 => Y01J" && TOTALYTD(y,'Période'[Période])>20000,1,0)

I try to check if the total value of y is greater than 20000 but when i use this calculated column is not working because its making comparison with each row of the table and not the total .

[tamerj1]

How does that relate to your original query?

[Anonymous]

Details : With catSum for CAT =A i have 108 for lastDate =05/01/2021 but if in my dataset i have for cat =A another Esp with lastdate=01/01/2022 the CatSum is gonna be the same and i want a catsum by year and cat too.

Thank you

[Anonymous]

Hi,

Sorry it's bad manipulation and the wrong query,

I try to use your measure and it's working but if i have two years it's make the total of all years How can i calculate the measure and get the result by year ?
After use the result in a calculated column and found which cat have the total mount by year greter than 100 ?
Thank you for your help

[tamerj1]

Hi @Anonymous 
Then use this code for be able to curry out your calculations per year

Last Date = 
VAR CurrentCatEspTable =
    FILTER (
        Data,
        Data[Cat] = EARLIER ( Data[Cat] )
            && Data[Esp] = EARLIER ( Data[Esp] )
                && YEAR ( Data[Date] ) = YEAR ( EARLIER ( Data[Date] ) )
    )
VAR Result =
    MAXX ( CurrentCatEspTable, Data[Date] )
RETURN
    Result

[Anonymous]

It's not working when i update the measure ^^

[tamerj1]

Hi
That was the updated code for the Calculated Column . Hear is the sample file https://www.dropbox.com/t/0m73Tr3O59lXfSSN

[tamerj1]

Hi @Anonymous 
You were absolutely right
this is the updated measure as well 

CatSUM = 
VAR CurrentYear = 
    YEAR ( VALUES ( Data[Date] ) )
RETURN
CALCULATE ( 
    SUM ( Data[Mount] ),
    REMOVEFILTERS(),
    VALUES ( Data[Cat] ),
    YEAR ( Data[Date] ) = CurrentYear,
    FILTER ( 
        ALL ( Data ),
        Data[Date] = Data[Last Date]
    )       
)

Updated file also here https://www.dropbox.com/t/oQsyHPnF1p2RqnOB