YTD Calculation on Certain Rows Only

[Anonymous]

Hi All,

This is my first post so apologies if I don't have the right format or etiquette 🙂 I am quite new to PBI but would like to think I am learning fast. I'm trying to calculate a YTD sales measure for a staff ID number but only based on certain fields. So in the screenshot below what I would want to calculate is a YTD SALES column (green) but only where the other column entries all match. For example, the rows highlighted in yellow would calculate to YTD, whereas those highlighted in orange won't because the state / city combination is different even though it is the same STAFF ID. T

I have tried various versions of this post but can't seem to make it do what I want 😞 

Solved: How to do cummulative sum based on group and date(... - Microsoft Power BI Community

Any help would be greatly appreciated!

Ian

[daxer-almighty]

Hi @Anonymous 

First, I can't even figure out whether you need a measure or a calculated column. If you need a measure, then you have to model your data in the right way in the first place. Good data modeling is crucial to any good - AND FAST - model. All the rows that qualify for your calculation should be marked with 1 (or True) and the other ones with 0 (False). You need a new column for this and if this is your fact table (as I think it is), it should always be hidden. Yes, all columns in fact tables should be hidden in a well-designed model. Once you've got the column, it's then easy to create the measure you want. VERY EASY in fact. The column I'm talking about should be created in Power Query, not in DAX.

By the way, if you want to really learn "fast" and in the right way, you should do yourself a favour and read THIS.