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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Aggregating calculated data

I am tring to aggregate data I have in a column by adding the data in the current row to all the previous rows. Sample data below shows the data in the Done column that I would like to aggregate to produce what I am showing in the Cumulative Done column:

MonthDoneCumuluative Done
January4040
February 40
March141
April 41
May2768
June67135
July12147
August49196
September113309
October115424
November83507
December 507

 

For my data, the Done column is actually a calculated column based on counts of the number of items that have been moved to Done for the given month. The Cumulative Done column is to represent how many have moved to Done YTD.

 

How do I calculate the Cumulative Done in Power BI so I can show it in a line chart.?

15 REPLIES 15
Ashish_Mathur
Super User
Super User

Hi,

 

It is quite easy to solve the problem if you have an actual date column in your data source.  If that is the case, then we can use the DATESYTD() function.  Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-cherch-msft
Employee
Employee

Hi @Anonymous

 

It seems you need a measure instead of calculated column. You may add index for your data in query editor. Then use 'sort by column'. Then you may get the measure as below. Please let me know if the solution makes sense.

Cumuluative Done =
CALCULATE (
    SUM ( Table[Done] ),
    FILTER ( ALL ( Table ), Table[Index] <= MAX ( Table[Index] ) )
)

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable


@v-cherch-msft wrote:

Hi @Anonymous

 

It seems you need a measure instead of calculated column. You may add index for your data in query editor. Then use 'sort by column'. Then you may get the measure as below. Please let me know if the solution makes sense.

Cumuluative Done =
CALCULATE (
    SUM ( Table[Done] ),
    FILTER ( ALL ( Table ), Table[Index] <= MAX ( Table[Index] ) )
)

1.png

 

Regards,

Cherie



Cherie, to be honest, I get confused by the difference between calculated columns and measures. I believe my Done column IS a measure and that is causing a problem with the formula you suggest. I cannot choose it for this part of your formula:

SUM ( Table[Done] ),

Plenty of columns show, but not my Done column. 

 

Here is how I get my Done column: Done = CALCULATE(COUNT(Issues[Key]),History[History New Value]="Done")

Anonymous
Not applicable


@Anonymous wrote:

@v-cherch-msft wrote:

Hi @Anonymous

 

It seems you need a measure instead of calculated column. You may add index for your data in query editor. Then use 'sort by column'. Then you may get the measure as below. Please let me know if the solution makes sense.

Cumuluative Done =
CALCULATE (
    SUM ( Table[Done] ),
    FILTER ( ALL ( Table ), Table[Index] <= MAX ( Table[Index] ) )
)

1.png

 

Regards,

Cherie



Cherie, to be honest, I get confused by the difference between calculated columns and measures. I believe my Done column IS a measure and that is causing a problem with the formula you suggest. I cannot choose it for this part of your formula:

SUM ( Table[Done] ),

Plenty of columns show, but not my Done column. 

 

Here is how I get my Done column: Done = CALCULATE(COUNT(Issues[Key]),History[History New Value]="Done")


Alright, so I changed my measure into a column and was able to apply your formula but I am still not getting the right results. Here is my formula: 

Cumulative Done =
CALCULATE (
SUM ( Issues[Monthly Done] ),
FILTER ( ALL ( Issues ), Issues[Month #] <= MAX ( Issues[Month #] ) )
)

 

Now it seems to just be adding them all and putting the result in every row. Here is what I am getting:

MonthMonth #Monthly DoneCumuluative Done
January1401476
March311476
May5271476
June6671476
July7121476
August8491476
September91131476
October101151476
November11831476

@Anonymous- I recommend you read this article on the difference between calculated columns and measures. What you are trying to do is not difficult, but I think you are missing some of the underlying concepts in how DAX works. Calculated Columns and Measures are very different in how they handle row and filter context, which are two of the hardest concepts to get your head around as a beginner. It took me a few months before I really grasped what those were doing, and it is key to just about everything you do if you are building your own formulas - be it in a table as a calculated column, or as a measure.

 

And unfortunately I don't have the October build of Power BI desktop to save it as an old format. I am surprised the Oct build cannot open a November file.

 

Do you have a personal PC you can install PBI desktop on to see how it works?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable


@edhans wrote:

@Anonymous- I recommend you read this article on the difference between calculated columns and measures. What you are trying to do is not difficult, but I think you are missing some of the underlying concepts in how DAX works. Calculated Columns and Measures are very different in how they handle row and filter context, which are two of the hardest concepts to get your head around as a beginner. It took me a few months before I really grasped what those were doing, and it is key to just about everything you do if you are building your own formulas - be it in a table as a calculated column, or as a measure.

 

And unfortunately I don't have the October build of Power BI desktop to save it as an old format. I am surprised the Oct build cannot open a November file.

 

Do you have a personal PC you can install PBI desktop on to see how it works?


@edhans,  I agree what I am trying to do should be simple but I may be complicating get the help I need by not sharing enough detail or not having enough knowledge to explain imy situation properly. I will take a look at the article you share but I'd also like to try to share more detail to see if this becomes obvious what I need to do.

 

First, I took your suggestion and installed the latest version of Power BI on my personal laptop. I was then able to view your file. I think the important distinction is that you created tables with the data I shared but in my case, these are visuals I created and some of the data does not exist in tables but are generated for the visual. For example, my Done data is generated by trhis formula:

 

Done = CALCULATE(COUNT(Issues[Key]),History[History New Value]="Done")

 

The Key field in the Issues table is unique and there are over 1,400 entires in my data. The History New Value field in the History table has multiple rows for each Key and there are several possible entries in the History New Value field for each key. The Month field is displayed from a full date and time field called Updated that I set in the Values section to just show the month. 

 

Although I have data spanning multiple years, in my visual, for the Done column, I am able to filter the display to only count the 2018 entries in the Updated field. Unfortunately, this does not seem to limit the count in the Cumulative Done field. With an earlier version of a formula I used (following), it seems to be counting all the entries across all the years:

 

Cumulative Done =

SUMX (

    FILTER ( Issues, Issues[Month #] <= EARLIER ( Issues[Month #] ) ),

    Issues[Monthly Done]

)

 

There does not seem to be a way to set this column to just show the 2018 cumulative counts in the filters, but I am hoping there is a minor tweak to this formula that will allow me to do it.

 

 

Hopefuly, these additional details clarifiy my issue and gives enough info to help me solve it. What do you think? Am I close, or do I need to go a whole different route?

 

 

Hi @Anonymous

 

Could you share your file so that we could help further on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Anonymous

 

You may create two measures as below:

Monthly Done2 =
CALCULATE (
    COUNT ( Issues[Key] ),
    History[History New Value] = "Done",
    YEAR ( Issues[Updated] ) = 2018,
    Issues[Issue Status] = "1.Done"
)
    + 0
Cumulative Done2 =
SUMX (
    FILTER ( ALL ( Issues ), Issues[Month #] <= MAX ( Issues[Month #] ) ),
    [Monthly Done2]
)

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable


@v-cherch-msft wrote:

Hi @Anonymous

 

You may create two measures as below:

Monthly Done2 =
CALCULATE (
    COUNT ( Issues[Key] ),
    History[History New Value] = "Done",
    YEAR ( Issues[Updated] ) = 2018,
    Issues[Issue Status] = "1.Done"
)
    + 0
Cumulative Done2 =
SUMX (
    FILTER ( ALL ( Issues ), Issues[Month #] <= MAX ( Issues[Month #] ) ),
    [Monthly Done2]
)

1.png

 

Regards,

Cherie


Cherie, this is awesome! Thank you so much. This gives me just what I need for 2018. And it also includes the months where there were no changes, which I need. I imagine it is just a quick edit to change the Monthly Done measure to change it to 2019. 

 

May I ask for one more suggestion? I will be sharing these reports on a published dashboard and I would like to give people the ability to set the date range so the cumulative count could start at any month, and run to any future month. Is it possible to do this with a slide bar like I used in other dashboards (which you can see in the file I shared with you)?

 

-Greg

Anonymous
Not applicable

Cherie, I have uploaded my data. The CFD page is where I am working on this dashboard. The Datekey table is a new one I created trying to get this to work. The other two tables are from our projects database, plus some columns and measures I have created.

 

Thank you for all your help with this.

 

-Greg

Hi @Anonymous

 

I cannot access the file. You can upload the .pbix file to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

Regards,

Cherie

 

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Cherie, I do not know how to mask the data, hough, I don't think anything sensitive is in it. Still, I would prefer not to have an open link posted on the board. Can you share an email so I can just send you the link? You can email me at Greg.Murphy@aew.com.

 

Thanks,

Greg

Hi @Anonymous

 

You may send the link via private message.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
edhans
Super User
Super User

@Anonymous- see the attached file. I added a date table to your data, then used the TOTALYTD() measure, which will reset itself every January. I added some fake data into the next year so you could see how that works.

 

This will also work if you show the data by quarter, week, or even day.

 

Total Done YTD = TOTALYTD([Total Done],('Calendar'[Date]))

I always recommend you create your own date table. You can see in the file how I did it in Power Query. You can add a lot more columns if you want (short month name, MMM-YY combination, quarter number, etc.)

 

Link to PBIX file.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable


@edhans wrote:

@Anonymous- see the attached file. I added a date table to your data, then used the TOTALYTD() measure, which will reset itself every January. I added some fake data into the next year so you could see how that works.

 

This will also work if you show the data by quarter, week, or even day.

 

Total Done YTD = TOTALYTD([Total Done],('Calendar'[Date]))

I always recommend you create your own date table. You can see in the file how I did it in Power Query. You can add a lot more columns if you want (short month name, MMM-YY combination, quarter number, etc.)

 

Link to PBIX file.


@edhans, I downloaded your file but can't open it. Apparently, I need to install the latest version of Power BI but I don't have access to do that. I am a consultant and they lock this down from me. I am using Version: 2.63.3272.40262 64-bit (October 2018). Would you be able to share it in a way that I can open it with this version?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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