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