DAX cumulative curve of number milestones with a progress value calculated directly

Anonymous — Fri, 31 May 2024 09:08:44 GMT

Hello to everyone !

edit the link to the file : https://drive.google.com/file/d/1vE7Oj-dpwqc8KkmtsGFljeFUdUvZBZ1k/view?usp=drive_link

My issue is easy and complexe at the same time.

I want count the progress of my project from a table where i register date at each update of my project.
My table look like this

ID	group	Source update	Date
1	group1	X1	XX/XX/XX
1	group1	X2	XX/XX/XX
2	group1	X1	XX/XX/XX
2	group1	X2	XX/XX/XX
3	group2	X1	XX/XX/XX
3	group2	X2	XX/XX/XX
4	group2	X1	XX/XX/XX
4	group2	X2	XX/XX/XX
5	group2	X1	XX/XX/XX
5	group2	X2	XX/XX/XX
6	group3	X1	XX/XX/XX
6	group3	X2	XX/XX/XX
7	group3	X1	XX/XX/XX
7	group3	X2	XX/XX/XX
8	group3	X1	XX/XX/XX
8	group3	X2	XX/XX/XX
9	group3	X1	XX/XX/XX
9	group3	X2	XX/XX/XX
10	group4	X1	XX/XX/XX
10	group4	X2	XX/XX/XX
11	group4	X1	XX/XX/XX
11	group4	X2	XX/XX/XX
12	group4	X1	XX/XX/XX
12	group4	X2	XX/XX/XX
13	group4	X1	XX/XX/XX
13	group4	X2	XX/XX/XX

The progress i want to calculate by group of milestones and i want that each group has the same weight ( modulo a factor)

For example the group 1 has 2 milestones ==> thus for each review i have 2 milestones

and for the group 2 i have 3 milestones etc etc

Each group has one factor of weight one the table below

group	factor
group1	1
group2	2
group3	1
group4	3

because there is not hte same number of milestones by group + there is a diffrent factor ==. the progress calculated for each group is not the same.

To do this thing in power query it's auite easy

1- I merge the table of factor on the main table

2- i merge the main table on the main table (on the key which is the group&reviewnumber) ==> allow me to agregate the table and calculate the number of milestones by group by review

3- i add a column where i multiply the factor divide by the number of milestone *5 (5 is the weight for the milestone at the end)

==> with that i have a progress for each row that i can easily show on a cumulative curve or an histogram by group or by ID.

The issue is that the second merge (main table on the main table) + aggregation to count the number of key take a loooooot of time.

I thing there is a way to do it with DAX but i am struggle.

For exemple i tried to create a mesure like that :

curve_cumul = var nbrtotal =calculate(count('Project curve (2)'[Development curve - group]),ALL('Calendar')) var factor = calculate(Max('Project curve (2)'[Development curve - factor]),ALL('Calendar')) var nbr = calculate(count('Project curve (2)'[Development curve - group]),'Calendar'[Date]<=MAX('Calendar'[Date])) return nbr/nbrtotal*factor*5

if i show this on a visual it's working when i put in legend the group ==>

for each group i have a cumulative curve that show the progess (modulo the factor that i had)

However when i don't use the legend, i would think that i had the cumulative curve of each group but in fact no ==>

I suppose that it take the maximum so it's not a sum of each group that normally bring me to 300 but just the max of the table that go to 15 which is factor max = 3 * 5 (nbr =nbrtotal in that case cause it's not filtering on group as the legend do)

++ i would like to have a curve for each review to compare them.

I fell that i do it wrong but i am not so far from the answer but i am struggle soooo
Thank you veryyyy much for your help by advance !

Florian

Re: DAX cumulative curve of number milestones with a progress value calculated directly

lbendlin — Thu, 30 May 2024 18:55:06 GMT

your sample data is not usable. Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: DAX cumulative curve of number milestones with a progress value calculated directly

Anonymous — Wed, 19 Jun 2024 14:23:51 GMT

Hi Ibendlin,
Thank you for your reply.

I updated the post and add a link to a pbi file.

Data are not screenshot except curves so normally everything is usable now.

Thanks and have agood day !

Re: DAX cumulative curve of number milestones with a progress value calculated directly

lbendlin — Wed, 19 Jun 2024 22:04:15 GMT

link asks for access. Please verify.

Re: DAX cumulative curve of number milestones with a progress value calculated directly

Anonymous — Tue, 16 Jul 2024 15:36:22 GMT

it's change. Normally now you have access !

topic Re: DAX cumulative curve of number milestones with a progress value calculated directly in DAX Commands and Tips

DAX cumulative curve of number milestones with a progress value calculated directly

Re: DAX cumulative curve of number milestones with a progress value calculated directly

Re: DAX cumulative curve of number milestones with a progress value calculated directly

Re: DAX cumulative curve of number milestones with a progress value calculated directly

Re: DAX cumulative curve of number milestones with a progress value calculated directly