Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Team,
I have 3 columns I need to know the percentage of the column in a measure.
% of completed from all the projects where sub project are = A & C.
Here is my data.
Project | Sub Project | Status |
1 | A | In progress |
1 | B | Completed |
2 | A | Completed |
2 | B | Delayed |
2 | C | Completed |
3 | A | In progress |
3 | C | Completed |
4 | A | Delayed |
4 | B | Delayed |
5 | A | Completed |
5 | B | Completed |
5 | C | Completed |
Thanks in adance !
Hi, @Anonymous
try to create a measure like this:
% of Status =
var _AC=COUNTROWS(FILTER(ALL('Table'),'Table'[Sub Project] in {"A","C"}))
var _count=CALCULATE(COUNT('Table'[Status]),FILTER(ALLEXCEPT('Table','Table'[Status]),'Table'[Sub Project] in {"A","C"}))
var _divide=DIVIDE(_count,_AC)
var _if=IF(NOT(MAX('Table'[Sub Project])in{"A","C"}),BLANK(),_divide)
return _if
or you want to count the status of A&C:
_countStatusOfAC =
var _count=CALCULATE(COUNT('Table'[Status]),FILTER(ALLEXCEPT('Table','Table'[Status]),'Table'[Sub Project] in {"A","C"}))
return IF(NOT(MAX('Table'[Sub Project])in{"A","C"}),BLANK(),_count)
Result:
Please refer to the attachment below for details
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , try on of the two measures
divide(countrows(filter(Table, Table[sub project] in {"A", "C"})),countrows(table))
or
divide(countrows(filter(Table, Table[sub project] in {"A", "C"})),countrows(allselected(table)))
Hey @Anonymous ,
the problems is the instructions are not very clear.
Do you want Project A and C completed divided by all projects? Or do you want it divided by all completed projects? Do you want the amount of lines in the tables or the amount of unique product-ids? Does a project have to have a sub project A or a sub project C or do they have to have both an A and a C sub project?
The better you describe your problem and also the result, the better we can help.
In your case it would already help when you tell the result. For example 75% should be the result or something similar and best to explain why. Then a lot of questions are clarified.
My interpretation is now you want the unique ProjectIDs of completed Projects that have a subroject A or C, divided by the amount of unique ProejectIDs that are completed:
% of Status =
VAR vCompletedAandC = CALCULATE( DISTINCTCOUNT( myTable[Project ] ), myTable[Sub Project] IN { "A", "C" } && myTable[Status] = "Completed" )
VAR vAllCompleted = CALCULATE( DISTINCTCOUNT( myTable[Project ] ), myTable[Status] = "Completed" )
RETURN
DIVIDE(
vCompletedAandC,
vAllCompleted
)
If that's not the case, please describe your desired result properly.
Thanks for your reply !
I want percentage of completed with only Subprojects A & C.
I have a added a visual which show percentage of complete is 50%, I want same in measure.
@Anonymous So does my measure work or not?
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |