Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Summarized and Count

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
Need Help
  • mdaatifraza5556's avatar
    mdaatifraza5556
    1 year ago

    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's avatar
    mark_endicott
    1 year ago

    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!

3 Replies

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    1 year ago

    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's avatar
    mark_endicott
    Super User
    1 year ago

    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!