Forum Discussion

mike87's avatar
mike87
Frequent Visitor
8 years ago
Solved

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 tim...
  • mede's avatar
    mede
    8 years ago

    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.

mede's avatar
mede
Resolver I
8 years ago

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.