Below is an image to describe what I need to do with a DAX measure. I essentially need to get a Sum of values ("Units Sold" in my example below) in a table that are in sequence and where all other column values are repeated. If any other column value changes, I need a new Sum to start accumulating.
To make this more complex, the value is in a fact table while each of the other columns are in dimension tables. The final output will be a Table visual with all of the dimension columns together with the fact table value all the way to the right.
Edit: Note that I am using a Live Connection to a Tabular SQL Model, so I am unable to create DAX columns or calculated columns in Power BI. I'm looking for a way to do this in a DAX measure.
Edit 2: As I continued to try to write the DAX, I realized something very crucial that I needed to point out here. My end goal is to have a table as a Power BI visual that shows the data in the correct sequence. I will be using this table visual to filter other visuals when the user selects rows in the table visual.
Can you post a representative sample of your data that can be copied and pasted? I want to understand how your data is laid out so that I present a solution that is useable. Are Sales Rep, Client and Month all their own dimension tables?
In general, what I would do however would be to start a measure with a SUMMARIZE. Then use ADDCOLUMNS to add what you would normally do as a calculated column using EARLIER to figure out if something has changed.
Proud to be a Datanaut!
Unfortunately, there are many reasons why I cannot post a sample of my data. Perhaps the most important reason being that I am contractually prevented from doing so.
Assume that Sales Rep is in the fact table with Units Sold. Assume that Client is is its own dimension table, and Month is also in its own dimension table.
Your suggested functions for the basic structure of the measure sound like they might do what I am looking for. Unfortunately, I have no experience with Add Columns, so I will do some reading and attempt to put something together.
Thanks for your help!