Percentage Column Help

[Anonymous]

Hi

I need some help with a dashboard i am creating. I have 1 data dump out of Primavera p6 but need to either have a seperate sheet or another way to look at the below columns and calculate a %.

1 - Planner name

2 - Activity Status

3 - Missing Logic

I need to show a graph to show each of the planners, the remaining activities (Activity status doesnt equal Complete) and "Missing Logic". This is probably really easy but its baffled me. As a % of the remaining activities. 

Please help.

Cheers, Keith 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Check this formula.

Measure = 
var remain = COUNTX(FILTER('Table','Table'[activity status]<>"finished"),'Table'[planner])
var missing = COUNTX(FILTER('Table','Table'[activity status]<>"finished"&&'Table'[logic]="missing logic"),'Table'[planner])
return
missing/remain

Result would be shown as below.

 

Best Regards,

Jay

[ryan_mayu]

@Anonymous 

please share the sample data and expected output

[Anonymous]

Im not sure if this will work or not. and cant share information due to sensitivity. There are a lot more columns in the spreadsheet im using such as. Activity Name, Duration, Project Name etc etc etc. but is roughly 24000 rows of information. Hopefully the screenshot of excel will give an indication of what im after. If not ill have to try a replicate the data wtih new data in BI as an example. 

[parry2k]

@Anonymous add the following measures to achieve the result

Activity = COUNTROWS('Table')

Remaining = CALCULATE([Activity],'Table'[Activity status]<>"Finished"))

Missing Logic = CALCULATE([Activity] ,'Table'[Logic]="Missing Logic")

% = DIVIDE ( [Missing Logic], [Remaining]

Drop Planner and above measures in a table visual and you will get the result, you can further tweak these measure as per your business logic, I guess this will get you started.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[ryan_mayu]

@Anonymous 

Try to create measures

total activity = COUNTROWS('Table')

Total remaining = CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[Planner]),'Table'[Activity status]<>"Finished"))

Missing Logic = CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[Planner]),'Table'[Logic]="Missing Logic"&&'Table'[Activity status]<>"Finished"))

%not linked = [Missing Logic]/[Total remaining]