Play Axis with time aggregation

[SA800029]

Hi everybody,

I'm trying to show the evolution of individual peformances for the workers (Blue Colars / BC) in our plant.

The point is to make sur it improves over time, and identify outilers who would need additionnal training or support.

The data :

The primary key is the workorder (WO) number. A WO respresent a certain activity to perfom on an airtcraft, it can be done by one or sevral BC. For each WO the BC(s) are granted a certain amount of time that we call TAI to perform the task.
The lynk WO - TAI is found in our first table called : ZZ84 wich comes directly from SAP.

BC are requiered to clock in and out each time they work on a specific WO. This allow us to get the actual (real) time spent on a workorder, called TPP (welcome to aerospace, where everything gets intricate acronym).

This information is found in a second table called : SOFI.

To calculate the performance we simply compute, the average of TPP/TAI for each OF perform by the BC in question.

To be sure we capture the individual contribution, and to keep things easy I only kept WO perfom by one BC for now.

The graph :

What I want to show on a scatter plot is the individual performance (Y axis) vs the experience of the BC, measure as the total amount of work perform (Sum of all the TAI)

The graph works fine, the poblem is when I want to animate the graph with play axis (based on when the WO was done), It only plot the point for wich there was available data this week. Whereas I'd like to see the cumulative effect as more and more WO comes in, to see if each points tends to converge around the horizontal line (Y = 1).

Does anybody knows how to do that ?

Thank you for your time and have a nice week-end,

Regard,

Yoan

[poetim]

Using Oracle, i created another calendar table this way:

select a.data, b.data from
(select a.data
from
(select to_date('01/01/2022','dd/mm/yyyy') + rownum - 1 as data
from dual
connect by rownum <= to_date('31/12/' || to_char(sysdate, 'yyyy'),'dd/mm/yyyy') - to_date('01/01/2022','dd/mm/yyyy') + 1) a) a
left join
(select a.data
from
(select to_date('01/01/2022','dd/mm/yyyy') + rownum - 1 as data
from dual
connect by rownum <= to_date('31/12/' || to_char(sysdate, 'yyyy'),'dd/mm/yyyy') - to_date('01/01/2022','dd/mm/yyyy') + 1) a) b
on a.data >= b.data

 Using this table, i connected the column named Data (in Brazil Date is wrote like Data) with the column Date from my original calendar Table and then into the Play Axis i used the column Data_Pai (mother/father date).

You can view this result in this link:

https://1drv.ms/v/s!Amo1U4TNOETg4SZ1hIQneSjAqOlm

[Yishai]

I have not yet found a way to animate a visual while cumulating the values in Power BI.

[pranavkansara]

Try removing the field you've set as 'legend'. I was getting the same error, removing the legend field worked for me.

[Phil_Seamark]

Hi @SA800029

You will just need to use measures that generate a cumulative value.

There are some good patterns to follow here  https://www.daxpatterns.com/cumulative-total/

Or if you share a little more about your tables, we can try and suggest some that may work for you.

[SA800029]

Hi Phil,

Thank's for the website there're some useful patterns.

Unfortunately I'm still stuck with the play axis. When I try the formula without any filter on the dates, I get the same result than previously, which is good news, but as soon as I start to filter (using a slicer or the play axis), I get either nothing or an error.

Here are pictures of my data model and the error message I have when I use the column "Jour" (days in French) in the SOFI table for Play Axis.

For the Y axis I use a measure with the following formula :

The X axis being simply the sum of TAI.

Perfo OF = AVERAGEX(VALUES(SOFI[OF]);CALCULATE(DIVIDE(SUM(SOFI[TPP]);SUM(zz84[TAI]));zz84[OP TERA]<>""))

Is the problem with my model and the relationship may be ?

PS 1 : I added the table "WorkOrder" to get a unique Key, because the same WO can appear in several line in both tables. Due to the fact BC clock in at each step.

PS 2 : don't mind the other lines in the tables I only used the attributes mentionned earlier for this analysis.

Thank you for your help and let me know if you need more information, unforntunately I won't be able to share the tables as they contain confidential material on our personnel.

Regard,

Yoan

[v-xjiin-msft]

Hi @SA800029,

Could you please share us your pbix file with One Drive or Google Drive if possible? So that we can dig deeper and make some proper tests.

Thanks,
Xi Jin.

[SA800029]

Good afternoon,

I prepared an anonym copy of my .pbix file so I can share it without any confidentiality breach, but I can't figure out how to join the document to my answer ?

We have neither google drive nor one drive installed, for security measures, so installing them is not an option unfortunately.

If you know how I can upload my files I'll do it gladly, otherwise I guess I'll have to hope someone can help me without it.

Thank again for your time, I really appreciate

Regard,

Yoan