Create Summy Table

[user900]

I have a large data set with Date and Status. Status includes Assigned, In Progress and Complete.  I want to create a dynamic summary of the count of Complete and Total by Month.  The total will be the count of all statuses.  Then I can calculate the percentage of how much progress is made in each specific month (Complete divided by Total).

Desired Result of Summary table:

Month	Complete	Total	%
Jan	0	1600	0%
Feb	600	2700	22.22%
Mar	400	3000	13.33%

Any suggestions?

P.S. I'm not an advanced user.

[Ashish_Mathur]

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number column
2. Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table
3. To your Table visual, drag Year and Month name from the Calendar Table
4. Write these measures

Total = countrows(Data)

Complete = calculate([Total],Data[Status]="Complete")

Complete (%) = divide([Complete],[Total])

Hope this helps.

[v-xinruzhu-msft]

Hi @user900 

Thanks for the solution @amitchandak  provided and I want to offer some more information for you to refer to.

Sample data

Create the following measures

Complete =
VAR a =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Status] = "Complete" )
RETURN
    IF ( a > 0, a, IF ( [Total] > 0, 0 ) )

Total = COUNTA('Table'[Status])

% = DIVIDE([Complete],[Total])

Then put the measures to a table visual.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@user900 , for the First three columns use Group by of Power Query, and for % create a measure in DAX

Divide(Sum(Table[Complete]), Sum(Table[Total])  )

https://learn.microsoft.com/en-us/power-query/group-by