Search code examples
excelexcel-formuladaxpowerpivot

How to realize ratio with powerpivot

I'm trying to do ratio with powerpivot but I have some problem...

I have to measures : time_spent & planned_hours

There is hierarchy like this : global => project => tasks => timesheet entries

It works with timesheet entries but when I drill-up to tasks, if there is multiple timesheet entries it's sum planned_hours and make my ratio wrong...

My question, how can I for :

  • timesheet entries => time_spent/planned_hours
  • tasks => SUM(time_spent)/planned_hours
  • project => SUM(time_spent) with tasks level / SUM(planned_hours) with task level
  • global => SUM(time_spent) with projet level / SUM(planned_hours) with project level

Thanks in advance for your help :) Arnaud

Solution

  • Solution has been find !

    Here is my solution, if somebody need something like that :

    IF(ISFILTERED('Requête'[ID Saisie Temps]);'Requête'[Somme de Heures passées]/'Requête'[Sum of Planned_Hours];'Requête'[Somme de Heures passées]/SUMX(SUMMARIZE('Requête'; 'Requête'[Nom tâche]; "Max Heures Facturées"; MAX('Requête'[Planned_Hours]));[Max Heures Facturées]))

    Best regards, Arnaud