cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adambalshaw
New Member

YTD Cumulative SUM of a measure

Hey,

 

I've got a measure [Frequency Rate] which is just DIVIDE([EventCount], [Headcount]) and I need to work out what the [Frequency Rate] is for each month (see table below) and then SUM the measure for all the months YTD. 

 

The figure I need to get to for the measure from the table would be 2.06 but I just keep banging my head against a wall on this one.

 

I've got DAX to figure out the table bit but I can't SUM the measure - any ideas? Hope that makes sense!

 

 

------

EVALUATE

SUMMARIZECOLUMNS(
    DimDate[PeriodName],
        FILTER(DimDate, DimDate[IsCurrentYear] = TRUE()),
    "Frequency Rate", [Close Calls By Headcount]
    )

 

which gives:

 

PeriodNameFrequency Rate
01/01/2022 00:00:000.189374262101535
01/02/2022 00:00:000.210484511517077
01/03/2022 00:00:000.22818523554347
01/04/2022 00:00:000.201570680628272
01/05/2022 00:00:000.226923692209247
01/06/2022 00:00:000.208005834211166
01/07/2022 00:00:000.108861796014921
01/08/2022 00:00:000.683333333333333
1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@adambalshaw not sure how your data looks like before but basically it's something like that:
CALCULATE(
SUMX
(
    VALUES(DimDate[PeriodName]),
    [Close Calls By Headcount]
    ),
DimDate[IsCurrentYear] = TRUE()
)
 
If you can't take it from here then share a sample file.
Anyways you should never use summarizecolumns in measure(only for queries / calculated tables). If you need the same technique then for measures you use the ADDCOLUMNS(SUMMARIZE(....)) pattern


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Showcase Report – Contoso By SpartaBI

Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@adambalshaw So it should essentially be a SUMX statement with a SUMMARIZECOLUMNS with a FILTER for all prior months in the current year. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

Hard to be specific without sample data. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks for replying! Yeah it was SUMX I needed to be using. And I've had a look through those links and I think they're very helpful - I'll definitely have a read through properly when I've got a minute in preparation for my next time intelligence issue!

SpartaBI
Community Champion
Community Champion

@adambalshaw not sure how your data looks like before but basically it's something like that:
CALCULATE(
SUMX
(
    VALUES(DimDate[PeriodName]),
    [Close Calls By Headcount]
    ),
DimDate[IsCurrentYear] = TRUE()
)
 
If you can't take it from here then share a sample file.
Anyways you should never use summarizecolumns in measure(only for queries / calculated tables). If you need the same technique then for measures you use the ADDCOLUMNS(SUMMARIZE(....)) pattern


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Showcase Report – Contoso By SpartaBI

Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Thanks for the reply, that was exactly it! Not sure why SUMX didn't occur to me but maybe I need to look further into in.

@adambalshaw my pleasure 🙂
Hey, check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. Please give it a thumbs up over there if you liked it 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors