How to create total count for WIP and completed in powerBI

[hiteshniranjan]

Hello Eveyone,

I am new to powerbi and trying to matrix with count of WIP( less than 100) and completed(eq to 100) from the following data. Please suggest.

[v-zhangti · Accepted solution]

Hi, @hiteshniranjan 

You can try the following methods.

Column:

Column A = IF([Col A%]=100,"Completed",IF([Col A%]<100,"WIP"))

Column B = IF([Col B%]=100,"Completed",IF([Col B%]<100,"WIP"))

New Table:

Table 2 = SUMMARIZE('Table','Table'[Column A])

The column names were changed to "Type" to make it easier to distinguish.

Column:

Count A = CALCULATE(COUNT('Table'[Column A]),FILTER('Table',[Column A]=EARLIER('Table 2'[Type])))

Count B = CALCULATE(COUNT('Table'[Column B]),FILTER('Table',[Column B]=EARLIER('Table 2'[Type])))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PVO3]

Hi

You coul try something like this

Measure1 = CALCULATE(COUNTROWS(Table),Table[Col A%] = 100)

Measure 2 = CALCULATE(COUNTROWS(Table),Table[Col A%] <> 100)

[hiteshniranjan]

Thanks POV, Post creating these Measure how can i display the Text in first col as WIP and Completed because i have no column for this , Actually i am not sure how can i add these measure to get the final output

[PVO3]

The easiest way is to:

- Create a table like this

- Create a matix visual, with this above column as columns

- In the matrix switch values to rows

- add these measures:

WIP =

SWITCH(TRUE(),

    SELECTEDVALUE('Table (3)'[Column1]) = "Col A%", CALCULATE(COUNTROWS('Table'),'Table'[Col A%] <> 100),

    SELECTEDVALUE('Table (3)'[Column1]) = "Col B%", CALCULATE(COUNTROWS('Table'),'Table'[Col B%] <> 100)

)

Completed =

SWITCH(TRUE(),

    SELECTEDVALUE('Table (3)'[Column1]) = "Col A%", CALCULATE(COUNTROWS('Table'),'Table'[Col A%] = 100),

    SELECTEDVALUE('Table (3)'[Column1]) = "Col B%", CALCULATE(COUNTROWS('Table'),'Table'[Col B%] = 100)

)

But if your willing to invest in more complicated options you should dive into calculation groups.