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

[Anonymous]

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

[Anonymous]

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 ! 

[lbendlin]

link asks for access. Please verify.

[Anonymous]

it's change. Normally now you have access ! 

[lbendlin]

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-...

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/1447...