Forum Discussion

user900's avatar
user900
Helper II
2 years ago
Solved

Create Summy Table

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:

MonthComplete Total%
Jan016000%
Feb600270022.22%
Mar400300013.33%

Any suggestions?

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

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    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.