Summarized and Count

[AllanBerces]

Hi good day can someone help me on my summarized table, basically i summarized my Table1 base on below DAX and I want to add column using count in one of my column in Table 1 the %_Complete

Summarized = SUMMARIZE('Table1','Table1'[Location], 'Table1'[Category], "Count-Category", COUNT('Table1'[Category]), "Hrs", SUM('Table1'[Hrs]))

Table1 (Long List)

DESIRED OUTPUT (Summarized of Table 1)

Thank you

[mdaatifraza5556 · Accepted solution]

Hi @AllanBerces 

Can you please the below DAX?

Create calculated table using below DAX.

Summarized =

SUMMARIZE(

    'Data',

    'Data'[Location],

    'Data'[Category],

    "Count-Category", COUNT('Data'[Category]),

    "Hrs", SUM('Data'[Hrs]),

    "Count_%_Complete_0",

        CALCULATE(

            COUNTROWS('Data'),

            'Data'[%_Complete] = 0

        ),

    "Count_%_Complete_100",

        CALCULATE(

            COUNTROWS('Data'),

            'Data'[%_Complete] = 100

        )

)

If this answers your questions, kindly accept it as a solution and give kudos.

[mark_endicott · Accepted solution]

Try this:

SUMMARIZE('Table1','Table1'[Location], 'Table1'[Category], "Count-Category", COUNT('Table1'[Category]), "Hrs", SUM('Table1'[Hrs]), "Count_%_0", CALCULATE(COUNTROWS('Data'),'Data'[%_Complete]=0)),"Count_%_100", CALCULATE(COUNTROWS('Data'),'Data'[%_Complete]=100)))

It'll add both the new columns you desire.

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[mdaatifraza5556 · Accepted solution]

Hi @AllanBerces 

Can you please the below DAX?

Create calculated table using below DAX.

Summarized =

SUMMARIZE(

    'Data',

    'Data'[Location],

    'Data'[Category],

    "Count-Category", COUNT('Data'[Category]),

    "Hrs", SUM('Data'[Hrs]),

    "Count_%_Complete_0",

        CALCULATE(

            COUNTROWS('Data'),

            'Data'[%_Complete] = 0

        ),

    "Count_%_Complete_100",

        CALCULATE(

            COUNTROWS('Data'),

            'Data'[%_Complete] = 100

        )

)

If this answers your questions, kindly accept it as a solution and give kudos.

[AllanBerces]

Hi @mark_endicott @mdaatifraza5556 thank you very much for the reply, result as i need.