Issue with Summarizing Status Count

[rocky09]

See below table, I have cat. column, where Users start working on each cat. and change the status to Inprogress, Complete and some times rework.

So, if a Cat. worked on 5 days and status will be In progress for 5 days, on 6th day , the work was completed. So, the status will becomes Completed.  Now, I am trying to count of Status.

Logic is:

* If any of the Cat. has Completed. The Cat. will show under Completed and it should counts only one.

* If any of the Cat. is still In Progress, the Cat. will show InProgress status and it should take count as one every it has 10 Previous inprogress days.

*Same logic appicable to rework.

See my sample data and the solution i am expecting.

Date	Cat.	Status
2-Oct-17	alpha_9383993	In Progress
3-Oct-17	Pulse_9387388	In Progress
4-Oct-17	Pulse_9387388	Rework
5-Oct-17	alpha_9383993	In Progress
6-Oct-17	alpha_9383993	Completed
7-Oct-17	Pulse_9387388	Completed
8-Oct-17	Oppo_tes_9383	In Progress
9-Oct-17	Oppo_Max_8977	Rework

Status	Count
Completed	2
In Progress	1
Rework	1

[v-ljerr-msft · Accepted solution]

Hi @rocky09,

Based on my test, the formula below should work in your scenario.

Count = 
VAR maxDate =
    CALCULATE (
        MAX ( Data[Date] ),
        FILTER ( ALL ( Data ), Data[Cat.] = EARLIER ( Data[Cat.] ) )
    )
VAR minDate =
    CALCULATE (
        MAX ( Data[Date] ),
        FILTER ( ALL ( Data ), Data[Cat.] = EARLIER ( Data[Cat.] ) )
    )
RETURN
    IF (
        Data[Status] = "Completed",
        1,
        IF ( Data[Date] = maxDate, 1 + DATEDIFF ( minDate, maxDate, DAY ) / 10, 0 )
    )

Here is the sample pbix file for your reference. 

Regards

[Ashish_Mathur]

Hi,

I have been able to solve this problem with a single calculated field formula.  Please allow me time until tomorrow to share my solution.  In the meantime, here is a screenshot of my solution

[rocky09]

Thank you so much.  Waiting for your kind reply.

[Ashish_Mathur]

Hi @rocky09,

You may refer to my solution here.

Hope this helps.

[rocky09]

Hi Ashish,

Thank you,  it is in excel. I am looking for a solution in Power Bi Desktop.

[Ashish_Mathur]

Hi @rocky09,

You can just import the data to PowerBI desktop.  File > Import > Excel workbook.

[rocky09]

I have older version of excel. So, I cannot see the measure you used in the excel. That is why i have asked you to provide pbi file.

[Ashish_Mathur]

Hi,

Surprisingly, when i import the Excel workbook contents into PowerBI desktop, i get a message saying that a table of multiple values was supplied where a single value was expected.  I get the perfect solution in Excel though.  Here is the calculated field formula i wrote

=IFERROR(COUNTROWS(FILTER(VALUES(Data[Cat.]),CALCULATE(LOOKUPVALUE(Data[Status],Data[Date],MAX(Data[Date])),ALL(Data[Status]))=VALUES(Data[Status]))),BLANK())

[rocky09]

Hi Thank you,

Now, i am getting error "A table of multiple values was supplied where a single value was expected."

[Ashish_Mathur]

Hi,

That is exactly what i mentioned in my previous post.  I get the same error.  It works fine in Excel though.  Looks like a bug in PowerBI desktop.

[rocky09]

oh.. will wait for someone to confirm if this is the actually bug with Power Bi Desktop as it is working fine in Excel. Weired.

[Zubair_Muhammad]

Hi @rocky09

One way of doing this .

In essence you want the count based on last status for each cat

First Add a calculated Column lets say "Last Date"

=
CALCULATE (
    MAX ( Table1[Date] ),
    FILTER ( table1, Table1[Cat.] = EARLIER ( Table1[Cat.] ) )
)

 Then another calculated Column named "Count"

=
IF ( Table1[Date] = Table1[Last Date], 1, 0 )

Now Pivot with Status in Rows and Sum of Count in Values

[rocky09]

Thank you for your kind reply.

However, It is not counting properly. It is ignoring some of the Completed, Inprogress and Rework.

[Zubair_Muhammad]

Hi @rocky09

After adding the 2 calculated columns, create a pivot table with "Status" on Row Field and "Count" on Value field

[rocky09]

Hi,

I am getting Completed count properly. But, for In-progress, I am getting all count. It should count only latest Status.