Forum Discussion

japlazaheras's avatar
japlazaheras
Regular Visitor
5 years ago

Aggregate quantities by category

Hi all,

I am having troubles with a simple topic. I have a table with several columns, one of them is the number of weeks of delay, another one is the quantity and another one is the year. I use table to show information aggregating info per category and year. Image below:

 

As you can see it is simple, P BI carries out all the aggregation, summarizing each of the categories in rows with quantities for each year.

The problem comes when I try to calcular aggregated values in cumulative way. I use the following measure:

 

Agg3 =
VAR currentIdx = SELECTEDVALUE(tentregapedidos[Salida-Confirmado])
return
CALCULATE(
SUM(tentregapedidos[Qty]),
ALLEXCEPT( tentregapedidos, tentregapedidos[Year], tentregapedidos[Deliv.Note Creation].[Quarter], tentregapedidos[Deliv.Note Creation].[Date], tentregapedidos[Profile], tentregapedidos[mot_ret], tentregapedidos[Plan] ),
VALUES(tentregapedidos[Salida-Confirmado]),
tentregapedidos[Salida-Confirmado] <= currentIdx
)
The result is the following:
 

 

My expected result for Agg3 in Yr 2020 would be 5042, 5745, 5745, 5752,5752,5752,5752,5815,6069,6073. So every row aggregates the quantity for the corresponding row plus the value from the previous one.
 
I tried several ways but no way. Any hint? I have created and Index for the table but I would like to make the calculation independently of dates as the sorting way for the table is based on Salida-Confirmado which would result as a category.
 
Thanks,
Arturo.

 

Need Help

4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    japlazaheras 

    Not sure how your model is built but, can you try the following measure?

    Agg3 =
VAR currentIdx = SELECTEDVALUE(tentregapedidos[Salida-Confirmado])
return
CALCULATE(
    SUM(tentregapedidos[Qty]),
    tentregapedidos[Salida-Confirmado] <= currentIdx,
    REMOVEFILTERS( tentregapedidos[Salida-Confirmado])
)

     

     

    • japlazaheras's avatar
      japlazaheras
      Regular Visitor
      5 years ago

      Hello,

      It almost worked. If I just make a table and I select in a filter outside the Year then it aggregates well. See picture below:

      However, the goal is to compare Years and Quarters so instead of a Table I am trying to have a matrix where columns are Years and the Quarter is selected in a filter (slicer) in the page. Adding this format, and selecting 2 years (if I select matrix but only one year it works the same than the picture above) the result is as follows:

      As you can see there are several bad effects, one is that blank / cero rows the context Year doesn't work so, for example, 2020 4 wks you get the 2021 0 weeks. Why 2021 0 weeks? It looks like the measure , if there is no values resets and takes the initial value not the previous one. One minor change on your proposal helps:

       

      Agg3 =
      VAR currentIdx = SELECTEDVALUE(tentregapedidos[Salida-Confirmado])
      return
      CALCULATE(
      SUM(tentregapedidos[Qty]),
      tentregapedidos[Salida-Confirmado] <= currentIdx,
      ALLSELECTED( tentregapedidos[Salida-Confirmado])
      )
      If I do not REMOVEFILTERS then the result is:

      If solves the context problem of the Year but if there is one row in a year with cero then the value is reset to the initial one, first row of the year.

       

       

       

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        japlazaheras 

        I will be able to find out the issue by looking at the model in your file, kindly share a sample PBIX file with the same structure and remove confidential data if any.

        You can save in a cloud drive like Google Drive or One Drive then share the link here.