Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
PBI5851
Helper V
Helper V

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. 
PBI5851_0-1701984571225.png
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 PBI5851_1-1701984801773.png 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. 
2 REPLIES 2
amitchandak
Super User
Super User

@PBI5851 , for that you need to dynamic segmentation on the measure and create three new measure to use bucket based on current month values

 

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

@amitchandak Thank you for that solution, but unfortunately, it will not work completely as (due to my incomplete info in the question), there is a second part of the request where the count_of_17 measure is to use the current ship month and next ship month measures itself. 
ex: if nextshipmonth+1 > currentshipmonth then 1 else 0. Using your solution, i dont have set parameter like you have in the start and end limit.  Any other suggestion on how to use the measure as a calculation parameter. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.