Forum Discussion
Sum data by month
Hi,
I'm fairly new to Power BI, so this might be an easy fix, but I haven't been able to "google" the answer.
I have a data set with reported incidents which is either resolved within the time limit or not. Unfortunately, I am not able to upload the data due to privacy. However, I have created a very simple example of the data in excel which should illustrate the problem.
I want to crate a new table and group/sum all cases which is resolved within the time limit by month, and calculate the percentage.
I have literally no clue how to do it with DAX.
Any help would be appreciated.
mike87 this is fairly easy and there are multiple ways:
For both ways make sure that your Date column is in date format. PBI recognizes your Date as a hierarchy of Year, Quarter, Month and Date; in a visual you can remove year, quarter and date leaving just Month. Alternatively you can create a calculated column for month by New Column >> Month = MONTH('Dim Date'[Date])
Easy way: Create a matrix, bring your date (Month) into rows, ResolvedWithinLimit into Columns, incident id (aggregation: count distinct) into values. Then display t the values (count distinct of incident ids) as a percentage of row total using quick measures.
A little bit more advanced way: create a new measures using the magical CALCULATE & ALL DAX functions.
Count of ResolvedWithinLimit = CALCULATE(DISTINCTCOUNT(incident_id), ResolvedWithinLimit = 1)
Count of all resolved = CALCULATE(DISTINCTCOUNT(incident_id), ALL(ResolvedWithinLimit)
ResolvedWithinLimit% = Count of ResolvedWithinLimit / Count of all resolved (change type to percentage)
Create a matrix where Month is in rows and drag these new measures into Values of your matrix.
2 Replies
mike87 this is fairly easy and there are multiple ways:
For both ways make sure that your Date column is in date format. PBI recognizes your Date as a hierarchy of Year, Quarter, Month and Date; in a visual you can remove year, quarter and date leaving just Month. Alternatively you can create a calculated column for month by New Column >> Month = MONTH('Dim Date'[Date])
Easy way: Create a matrix, bring your date (Month) into rows, ResolvedWithinLimit into Columns, incident id (aggregation: count distinct) into values. Then display t the values (count distinct of incident ids) as a percentage of row total using quick measures.
A little bit more advanced way: create a new measures using the magical CALCULATE & ALL DAX functions.
Count of ResolvedWithinLimit = CALCULATE(DISTINCTCOUNT(incident_id), ResolvedWithinLimit = 1)
Count of all resolved = CALCULATE(DISTINCTCOUNT(incident_id), ALL(ResolvedWithinLimit)
ResolvedWithinLimit% = Count of ResolvedWithinLimit / Count of all resolved (change type to percentage)
Create a matrix where Month is in rows and drag these new measures into Values of your matrix.
Create a column:
Month = MONTH([Date])
Create a table visualization and put Month and ResolvedWithinLimit (sum). You can also do a "Show As" "Percent of grand total".
