How to create a table of distinct values and columns from an existing table and calculated columns?

[Anonymous]

I have a data table that is created from merging several queries together. I was to use this data table that is an aggregation of many queires to create a simpler table that will be needed for some final calculations.

The table created from queries

Model Type	Model ID	Work Order Count	Strategy Compliant
A	112	3	Y
B	113	2	N
C	114	5	Y
A	115	4	Y
A	115	3	N

The table I want to create from the above

Distinct Model Type	Distinct Model ID	Sum Work Order Count	Count  Model ID	Count Model ID Strategy Compliant
A	112	3	1	1
A	115	7	2	1
B	113	2	1	0
C	114	5	1	1

Then I would like to add calculated columns like % compliant which would be the last column divided by the second to last column.

I believe to start this process I will need to use DAX formulas to reference the first table. Or is it possible to create this table in the query editor?

[V-pazhen-msft · Accepted solution]

@Anonymous 

Summarize should work, but I think you need to change a bit in the formula above.

Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[Model Type],
    'Table'[Model ID],
    "Sum Work Order Count", CALCULATE (
        SUM ( 'Table'[Work Order Count] ),
        ALLEXCEPT ( 'Table', 'Table'[Model ID] )
    ),
    "Count Model ID", COUNT ( 'Table'[Model ID] ),
    "Count Model ID Strategy Compliant", COUNTX (
        FILTER ( 'Table', 'Table'[Strategy Compliant] = "Y" ),
        'Table'[Model ID]
    )
)

 
Best,
Paul

[amitchandak]

Try summarize

summarize(table,table[Model Type],table[Model ID],"Sum Work Order Count",Table[Work Order Count],"Count  Model ID", count(Table[Model ID])
, "Strategy Compliant",countx(filter(table,table[Strategy Compliant]="Y"), Table[Model ID]))

https://docs.microsoft.com/en-us/dax/summarize-function-dax

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[mwegener]

Hi @Anonymous ,

i think you can use Power Query with Group-By

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.