Forum Discussion

harshadrokade's avatar
harshadrokade
Post Partisan
7 months ago
Solved

Table visual with data from Data tab + measures

Hi,   I have below data.    Project name 1-May-25 1-Jun-25 1-Jul-25 1-Aug-25 1-Sep-25 1-Oct-25 1-Nov-25 1-Dec-25 1-Jan-26 A 23 39 14 20 14 17 33 35 36 B 14 37 28 35...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 months ago

Hi,

If Table 1 shows the shape of the input data table, then you will have to first "Unpivot Other Columns" in Power Query and then build a matrix visual.  But in the matrix visual, if you want to add 4 additional columns (YTD and the last 3 column of your Table 2), then you may have to use calculation groups.

harshadrokade's avatar
harshadrokade
Post Partisan
7 months ago

Can u pls help me to understand how the calculation groups can help me to achieve this?

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 months ago

    Hi,

    Could you share sample data and the logic of your forecast calculations. 

    • harshadrokade's avatar
      harshadrokade
      Post Partisan
      6 months ago

      Data is same as I shared in first port.

      Logic for Forecasted value-
      Assuming I have a column with the actual values for each month:

       

      Month Value

      Jan100
      Feb120
      Mar140
      Apr160
      May180

       

      I will calculate the average monthly growth rate as follows:

      1. Calculate the growth rate for each month:

        • Feb: (120 - 100) / 100 = 20%
        • Mar: (140 - 120) / 120 = 16.67%
        • Apr: (160 - 140) / 140 = 14.29%
        • May: (180 - 160) / 160 = 12.5%

      2. Calculate the average monthly growth rate:

        • (20 + 16.67 + 14.29 + 12.5) / 4 = 15.89%

      3. Calculate the forecast for the 6th month:

        • Forecast = 180 * (1 + 15.89/100) = 208.60