CODEWITHSUNDEEP
powerbidax
Desert Spider
Desert Spider

Reputation: 778

Summarize table

I have a table that I want to pull percentage of total information from. The table has 5 columns (Date, User, LoadNbr, Approval and Week)

Date  User Id  Load Number  approval
4/1/2018  ABC123  111A  Auto
4/1/2018  ABC123  123A  Manual
4/1/2018  DEF456  234A  Auto
4/1/2018  ABC123  122B  Manual
4/1/2018  DEF456  756F  Manual
4/1/2018  ABC123  855F  Auto
4/2/2018  DEF456  684D  Auto
4/2/2018  ABC123  989S  Manual
4/2/2018  ABC123  203T  Manual
4/2/2018  ABC123  300Y  Manual
4/2/2018  DEF456  989Q  Auto
4/3/2018  IJK899  456A  Auto
4/3/2018  IJK899  632J  Manual
4/3/2018  IJK899  258G  Auto

I am trying to have a summary table that will populate each time up connect to a new source that would show something like the following;

Date      Auto  Manual  Total  Auto%
4/1/2018  3     3       6      50.00%
4/2/2018  2     3       5      40.00%
4/3/2018  2     1       3      66.67%

I know this is a remedial question and apologize for my limited abilities. any help is much appreciated.

Upvotes: 0

Views: 74

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You can do this in DAX using SUMMARIZECOLUMNS and ADDCOLUMNS to create a new summary table:

Summary =
    ADDCOLUMNS(
        SUMMARIZECOLUMNS(Table2[Date],
            "Auto", CALCULATE(COUNTROWS(Table2), Table2[Approval] = "Auto"),
            "Manual", CALCULATE(COUNTROWS(Table2), Table2[Approval] = "Manual")),
        "Total", [Auto] + [Manual],
        "Auto%", DIVIDE([Auto], [Auto] + [Manual]))

You can also do it in the query editor:

  1. Remove the User Id column.
  2. Pivot on the approval column. (Select that column and do Transform > Pivot Column using Load Number as the Values Column.)
  3. Create a custom column for Total. (Add Column > Custom Column and use = [Auto] + [Manual] for the formula.)
  4. Create a custom column for Auto%. (= [Auto] / [Total])

Upvotes: 1

Related Questions