Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

SUM by category

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 ...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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!

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    How does that relate to your original query?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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's avatar
      Anonymous
      Not applicable
      4 years ago

      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