Forum Discussion

PBI5851's avatar
PBI5851
Helper V
2 years ago

Using a measure in a summarize column

Hello, I am trying to build a forecast model and below is the raw data. 

TM_NameShippedCount
John10
jack30
Jim24
Jordan35
Jackie20
Jeanie10
Joe8
James15

I have created three measures. 

Current Ship Month = CALCULATE(sum(CountTable[ShippedCount]))
 
Next Ship Month+1 =
var __table = ADDCOLUMNS(SUMMARIZE(CountTable, CountTable[TM_Name]), "@CurrentShipMonth+1",[Current Ship Month])
Return
    SUMX(
        __table,
        IF([@CurrentShipMonth+1] <= 35, [Current Ship Month]+([Current Ship Month]*0.10), [Current Ship Month]+ ([Current Ship Month]*.20))
    )
 
Next Ship Month+2 =
var __table = ADDCOLUMNS(SUMMARIZE(CountTable, CountTable[TM_Name]), "@CurrentShipMonth+2",[Next Ship Month+1])
Return
    SUMX(
        __table,
        IF([@CurrentShipMonth+2] <= 35, [Current Ship Month]+([Current Ship Month]*0.15), [Current Ship Month]+ ([Current Ship Month]*.25))
    )
 
I get the data like below. 
Now i need to create a table which should give me under each of the columns, the number of TM's whose count value is more than 17. The expected visual should be like  and ideally lined up under the respective columns. 

 

1) Is it possibile to get Month and Year value dynamically (20203-12, 2024-01, 2024-02) instead of Current Ship Month , Next ShipMonth+1 etc.
2) How do i get the Count_more_than17 as I am unable to use the measures created in another summarize table to re-use it for calclation. 
 
Any recommendations please. 
Need Help

2 Replies