Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I hope someone may assist me with this...
Currently I have a table of projects that can be Won, Lost or No Realized, the column of this os really exisiting, the problem that I have is that the same project can have No Realized and Won Status in different dates, or Not Realized and Lost Status. However, I would like to know how many orders I have with No Realized Status if the project doesn't have status of Won or Lost previously
example of the table
-----------------------------
Project | Status
A | No Realized
A. | Won
B | No Realized
C. | No Realized
C. | Lost
D. | No Realized
D. | Lost
E. | No Realized
F | No Realized
---------------------------
So the result should be:
won projects: 1
lost projects: 2
No realized projects: 3
Solved! Go to Solution.
Hi @gkarlo,
Create a calculated column like this:
StatusValue =
IF (
SampleData[Status] = "No Realized",
1,
IF ( SampleData[Status] = "Lost", 2, 3 )
)
And then create 3 measures like this:
No of Won =
COUNTROWS (
FILTER (
SUMMARIZE (
SampleData,
SampleData[Project],
"MaxStatus", MAX ( SampleData[StatusValue] )
),
[MaxStatus] = 3
)
)
No of Lost =
COUNTROWS (
FILTER (
SUMMARIZE (
SampleData,
SampleData[Project],
"MaxStatus", MAX ( SampleData[StatusValue] )
),
[MaxStatus] = 2
)
)
No of No Realized =
COUNTROWS (
FILTER (
SUMMARIZE (
SampleData,
SampleData[Project],
"MaxStatus", MAX ( SampleData[StatusValue] )
),
[MaxStatus] = 1
)
)
The same can be achieved using RANK formula also.
Tested:
Upvote and accept as a solution if it helped!
Hi @gkarlo,
Sorry about that. I missed to replace COUNTROWS.
No of No Realized =
SUMX (
FILTER (
ADDCOLUMNS(
SUMMARIZE (
SampleData,
SampleData[Project],
"MaxStatus", MAX ( SampleData[StatusValue] )
),
"SumProject",SUMX(RELATEDTABLE(ProjectList),ProjectList[Price])
),
[MaxStatus] = 1
),
[SumProject]
)
hi Govindarajan,
I tested it, so far it worked very nice... thanks for your support 👍
Hi @gkarlo,
Create a calculated column like this:
StatusValue =
IF (
SampleData[Status] = "No Realized",
1,
IF ( SampleData[Status] = "Lost", 2, 3 )
)
And then create 3 measures like this:
No of Won =
COUNTROWS (
FILTER (
SUMMARIZE (
SampleData,
SampleData[Project],
"MaxStatus", MAX ( SampleData[StatusValue] )
),
[MaxStatus] = 3
)
)
No of Lost =
COUNTROWS (
FILTER (
SUMMARIZE (
SampleData,
SampleData[Project],
"MaxStatus", MAX ( SampleData[StatusValue] )
),
[MaxStatus] = 2
)
)
No of No Realized =
COUNTROWS (
FILTER (
SUMMARIZE (
SampleData,
SampleData[Project],
"MaxStatus", MAX ( SampleData[StatusValue] )
),
[MaxStatus] = 1
)
)
The same can be achieved using RANK formula also.
Tested:
Upvote and accept as a solution if it helped!
Hi again 👋👋,
if I would like to have the three measures in one column chart, how could I do it? cause in x-axes is not possible on power BI... something like this, I would really apreciate your support 😁
Thanks 🙏... btw I have tried to follow that you did with respect on counting the number of projects but regarding the price but I didn't have any success,
DATASAMPLE TABLE
Project | Status.
A | No Realized.
A. | Won.
B | No Realized.
C. | No Realized.
C. | Lost.
D. | No Realized.
D. | Lost.
E. | No Realized.
F | No Realized.
---------------------------
PROYECTLIST TABLE
Project | Price
A | 100€
A. |100€
B | 50€
C. | 50. €
C. | 50 €
D. |150 €
D. | 150€
E. |50€
F | 50€
So the result should be:
won projects: 100€
lost projects: 200€
No realized projects: 150€
For example only I tried to change the given measure in order to sum all prices with respect on this status:
No of No Realized = COUNTROWS ( FILTER ( SUMMARIZE ( SampleData, SampleData[Project], "MaxStatus", MAX ( SampleData[StatusValue] ) ), [MaxStatus] = 1 ) )
Instead of COUNTROW, I put SUMX
and for the table SampleData, I put another table that contains the price (different to the table of projects and status, but those have a linked relationship)
I don't know which is the missing step or in this case there is another way to get the revenue value. Thanks for the given time as well and I hope you may help me 🙏
Hi @gkarlo,
Can you try like this:
No of No Realized =
COUNTROWS (
FILTER (
ADDCOLUMNS(
SUMMARIZE (
SampleData,
SampleData[Project],
"MaxStatus", MAX ( SampleData[StatusValue] )
),
"SumProject",SUMX(RELATEDTABLE(ProjectList),ProjectList[Price])
),
[MaxStatus] = 1
)
)
Thanks, I put the code but the measure show only the number of projects but not the sum of prices 😅(as the previous code),
Hi @gkarlo,
Sorry about that. I missed to replace COUNTROWS.
No of No Realized =
SUMX (
FILTER (
ADDCOLUMNS(
SUMMARIZE (
SampleData,
SampleData[Project],
"MaxStatus", MAX ( SampleData[StatusValue] )
),
"SumProject",SUMX(RELATEDTABLE(ProjectList),ProjectList[Price])
),
[MaxStatus] = 1
),
[SumProject]
)
No worries 😅, thanks too much, it works correctly 🙌
User | Count |
---|---|
53 | |
35 | |
20 | |
16 | |
15 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |