Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

matrix visual add measure column

I have a requirement that I need to show the Volumes for the last 36 months and YTD Volume in the matrix table for product and product group as shown in the pic, issue is I am not able to add the YTD Volume measure column in the matrix table, if I add it is also distributed with monthly volume.

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Anonymous ,

    I created some data:

    Here are the steps you can follow：

    1. Create calculated table.

    Table 2 =

var _summarize=

SUMMARIZE(
    'Table_true',
    'Table_true'[Product Group],
    'Table_true'[product],
    "Date"," YTD Volume",
    "Amount",
    CALCULATE(SUM('Table_true'[Amount]),
    FILTER(ALL('Table_true'),'Table_true'[Product Group]=EARLIER('Table_true'[Product Group])&&'Table_true'[product]=EARLIER('Table_true'[product])&&
    'Table_true'[Date]>=EOMONTH(TODAY(),-36)&&'Table_true'[Date]<=TODAY())))
    return
    UNION('Table_true',_summarize)

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , This can come in grand total column.

     

    if(isinscope(Table[Month Year]), [measure], calculate([measure]), datesytd('Date'[Date])) )

     

    prefer to use date table

     

    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Amith, thanks for your reply, but I can't show 36 months of  data grand total as YTD 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    I created some data:

    Here are the steps you can follow：

    1. Create calculated table.

    Table 2 =

var _summarize=

SUMMARIZE(
    'Table_true',
    'Table_true'[Product Group],
    'Table_true'[product],
    "Date"," YTD Volume",
    "Amount",
    CALCULATE(SUM('Table_true'[Amount]),
    FILTER(ALL('Table_true'),'Table_true'[Product Group]=EARLIER('Table_true'[Product Group])&&'Table_true'[product]=EARLIER('Table_true'[product])&&
    'Table_true'[Date]>=EOMONTH(TODAY(),-36)&&'Table_true'[Date]<=TODAY())))
    return
    UNION('Table_true',_summarize)

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly