Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

% of status

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 ProjectStatus
1AIn progress
1BCompleted
2ACompleted
2BDelayed
2CCompleted
3AIn progress
3CCompleted
4ADelayed
4BDelayed
5ACompleted
5BCompleted
5CCompleted

 

Thanks in adance !

 

7 REPLIES 7
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1627277023619.png

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.

amitchandak
Super User
Super User

@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)))

Anonymous
Not applicable

@amitchandak ,

I want % of Status = Completed and Project A&C

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.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
Anonymous
Not applicable

@selimovd ,

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.

image.png

 

@Anonymous So does my measure work or not?

Anonymous
Not applicable

@selimovd ,

No this is not working.

image.png

image.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.