Search code examples
datepowerbidaxsummarize

PowerBI line chart by month

I want to create a line chart that summarizes a percentage by month. The table has a column of type Date like 7/11/24 and a column of No/Yes to compute the percentage of Yes's out of the total.

Example:

graph example

My thought was to create a new column for month, then a new table that aggregates (count of 1s) / total groupby month, then use that for the line table. Is there any easier way? And should I use STARTOFMONTH instead of the auto-generated table[dischargedate].[Month] because that only specifies month without year?

Here's what I have so far, but it isn't particularly elegant especially compared to the equivalent SQL:

New column

Discharge Month = STARTOFMONTH(index[dischargedate])

New table

Table = SUMMARIZE(index, index[Discharge Month], 
    "Successful Followup %", CALCULATE(COUNTROWS(index), index[followup]="Yes") / COUNTROWS(index))

SQL-like idea (not tested):

SELECT SUM(CASE WHEN followup = 'yes' THEN 1 ELSE 0 END) / COUNT(*)
FROM index
GROUP BY MONTH(dischargedate), YEAR(dischargedate)
Solution

  • You don't need to create a new aggregated table.

    You will need a Month column (or a Year-Month). Either create that column as you have above or consider to having a Calendar/Date table and have a relationship to your index table. See Create date tables in Power BI Desktop.

    For your Yes/No % - create a new Measure similar to:

    % Followup Yes = 
  DIVIDE(
    CALCULATE( COUNTROWS(index), index[followup]="Yes"),
    COUNTROWS(index)
  )

    You can then use this new measure in your chart.