Measure with Months in 2023 using Filter, GroupBy and SumX.

[Lamarandteddy]

Hello all,

I am fairly new to Power BI and trying to produce an measure expression which returns all months in 2023 that have a budget value greater than $440K. The below coding I have attempted doesn't produce an error, but doesn't list any specific list of months after I created the measure in the output. When I created the table first using a visulization & filter and did some reverse engineering on it, it is supposed to list January, April, July & October 2023 inside of the table. I want to also note that the Excel dataset I imported into Power BI contains a Date Hierarchy column underneath month that is in a mm/dd/yyyy format and has Base Fields of Year, Quarter, Month and Day. Can someone assist me in what I am doing incorrectly on this and provide the correct code? 

AggregatedBudgetMonthsOver440K =

SUMX(

    GROUPBY(

        FILTER(

            'FP&A Metrics',

            YEAR('FP&A Metrics'[Month]) = 2023 &&

            'FP&A Metrics'[Exercise Type] = "Budget" &&

            'FP&A Metrics'[Value] >= 444000

        ),

        'FP&A Metrics'[Month],

        "TotalValue", SUMX(CURRENTGROUP(), 'FP&A Metrics'[Value])

    ),

    [TotalValue]

)

[Jonvoge]

Hi Lamarandteddy

Can you show a screenshot of the visualisation you are trying to create? Is it just a table? And what fields/measures are you putting in it?

It sounds to me like you expect your Measure to return your months. However, your current measure is outputting the SUMX of [Total Amount], and not any months by itself. This means, that in order to use it to "filter" your Months, you will need to put both your Month and your Measure in a Table visualisation.

Perhaps I misunderstood your visualisation needs? If so, feel free to supply additiona information.

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data

[Lamarandteddy]

 Here you go Jonvoge

[Lamarandteddy]

Additionally, I have gotten closer. Here is my updated code: 

AggregatedBudgetMonthsOver440K =

FILTER(GROUPBY(FILTER('FP&A Metrics', [Exercise Type] = "Budget" && MONTH([Month])=1 && YEAR([MONTH])=2023), "Aggregate Months Over 440K", SUMX(CURRENTGROUP(), [Value])), [Aggregate Months Over 440K]>444000

)

However, I put 1 for January in the Month area, but not only do I need to analyze all months in 2023(1-12), I need all 4 months with over 444000 to be listed.