running total of a weighted measure

[Anonymous]

hi !

i´ve trying to solve this problem during the last two weeks and its time to request for help. 

I have 2 queries:

The first has a list of documents (with a standard name) with a weight depending on its importance and a planned date.

The second comes from sharepoint and is the list of documents that a contratist load in a sharepoint repository (there is a flow that add a line in a sharepoint list when it is loaded in the folder).  The documents can be loaded before or after of the planned date.

Every document is assingned to a proyect , so there are a lot of documents of a lot of projects. 

I need to do a S-curve with the planned (query 1) and real (query 2) data, and i need to be able of use a slicer to calculate the S-curve for a single project or a group of projects.

Since i have two queries with different dates... I made an additional query with CALENDAR , with the minimun and maximun dates of the other two queries:

FECHAS = CALENDAR(IF(MIN('masterplan'[FECHA PLAN])<MIN(listaDocumentos[FechaSubida]);MIN('masterplan'[FECHA PLAN]);MIN(listaDocumentos[FechaSubida]));IF(MAX('masterplan'[FECHA PLAN])>MAX(listaDocumentos[FechaSubida]);MAX('masterplan'[FECHA PLAN]);MAX(listaDocumentos[FechaSubida])))

The questions is... how can i weight and accumulate the planned values and graph it with the calendar query as X axis?

In this moment i have this measeure that can weight the graph depending on the project(s) selection (the sum of the values always is 100%)

noacum = CALCULATE(SUMX(masterplan;masterplan[axp])/CALCULATE(SUM(masterplan[axp]);ALLSELECTED(masterplan)))

but i have not achieved to accumulate it.

can anyone help me!!! please!

[CR]

Hi @Anonymous

Could you please display A simple table for each query (table1 and Table2) and what you need to get at the final. Just a simple set of data will help us a lot to solve this issue.

If I understand well, you get Planned Date in Table1 and Real (Date) in Table2. And you want to get, at the final, an S-Curve for Actual Date and an S-Curve for Real Date in a same chart ? If it is similar to this case, you need to append the tables to get a new one.

Table1

PROJECT	DOC-REF	PLANNED DATE
P1	DOC1	03/12/2018
P1	DOC2	04/12/2018
P2	DOC3	04/12/2018
P2	DOC4	05/12/2018

Table2

PROJECT	DOC-REF	REAL DATE
P1	DOC1	13/12/2018
P2	DOC3	14/12/2018
P3	DOC5	14/12/2018
P3	DOC6	10/12/2018

New table with Append as new

PROJECT	DOC-REF	PLANNED DATE	REAL DATE
P1	DOC1	03/12/2018	null
P1	DOC2	04/12/2018	null
P2	DOC3	04/12/2018	null
P2	DOC4	05/12/2018	null
P1	DOC1	null	13/12/2018
P2	DOC3	null	14/12/2018
P3	DOC5	null	14/12/2018
P3	DOC6	null	10/12/2018

Then you can unpivot the columns containing dates, etc. to get what you need for a S-Curve.

But, before going through that, please clarify with this kind of simple tables.

Regards,

CR

[Anonymous]

even if I append it, i cant make to work the running total. 

Look: 

The yellow part in the formula , is the weighting process. in fact is the "AVANCE PLAN" line in the graph. But when i try to make the running total , that is the whole formula, the result is 100% ( "acumAPP" line).

just to clarify: AXP is a column that multiply the advance percentage of the document with a value that represent the weight of the document on the project.

[CR]

I understand it doesn't work but could you please share an Excel file with samples for both tables ? We'll try to figure out.

I think indeed the solution is not coming from a table containing all dates. You make a relationship but it doesn't seem useful. This is the reason why we need data to work on it. Please share it !

Regards,

CR

[Anonymous]

---

@CR wrote:

I understand it doesn't work but could you please share an Excel file with samples for both tables ? We'll try to figure out.

I think indeed the solution is not coming from a table containing all dates. You make a relationship but it doesn't seem useful. This is the reason why we need data to work on it. Please share it !

Regards,

CR

---

ok! sooo..

there is a link where you can find some documents:

files

I have "MASTER" excels that contain the planned data of each project. In the first sheet of the master document there is a list of documents with its weights (sum of all the document weights in a Project is 100%) , and a set of columns that represent the hours of work of certain profiles (P1,P2,P3…).

In the second Sheet you will find the same documents and the planned load dates of the versions of each document (A1, B1 AND V0 are versions of the document and each one have a weight (give 25%,35% and 40%). so a document has 3 versions that represent the whole document; and the document itself have a weight with respect to the other documents. 

The masters are saved in the same folder and are combined in power bi: 1 query for the first sheet and another query for the second. ( I relate the queries by the document code)

Despite the fact that each document has an own weight, i need a way to weight the document not only in his own Project but with a lot of documents of the other projects (cause I need a S-curve that may be filtered by a single Project or all the portfolio, or a group of Projects). I do it through the file CONTRACT- PROFILE COST , that has the prices of each profile work hour (p1,p2…) for each contract.

So I calculate a total cost of the document like…

TOTAL COST = masterDatos[P1 ]*RELATED(Contratos[PERFIL1])+masterDatos[P2]*RELATED(Contratos[PERFIL2])+masterDatos[P3]*RELATED(Contratos[PERFIL3])+masterDatos[P4]*RELATED(Contratos[PERFIL4])+masterDatos[P5]*RELATED(Contratos[PERFIL5])+masterDatos[P6]*RELATED(Contratos[PERFIL6])+masterDatos[P7]*RELATED(Contratos[PERFIL7])

Being “masterDatos” the masters  sheet1 query and “contratos” the contract-profile cost query. ( perfil= profile in Spanish).

And I calculate a weight value by multiplying the weight value in the masters  sheet1 query by the TOTAL COST ( PONDERACION = WEIGHT):

WEIGHT VALUE = masterDatos[PONDERACION]*masterDatos[COSTOTOTAL]

Finally, in the masters sheet 2 query (planned dates ), I unpivot the data to obtain a table like this

Where each row is a version of a document. So i can calculate the planned advance for each version like this:

AVANCEPLAN = IF(masterplan[VERSION]="A1";RELATED('masterDatos (2)'[PONDERACION])*0,25;IF(masterplan[VERSION]="B1";RELATED('masterDatos (2)'[PONDERACION])*0,4;IF(masterplan[VERSION]="V0";RELATED('masterDatos (2)'[PONDERACION])*0,35;0)))

So .. we arrive now to the question.

Im trying to do  a graph that allow me  (1) make a S-curve that may be filtered by a single, a group or all the projects in the portfolio. To do that I have to weight the values because is not the same a project with 10 documents of USD$1000, that a project with 2 documents of $100USD (obviously , a s-curve have to be a running total).

I achieved to weight the values through this formulas:

Acum (measure)  = CALCULATE(SUMX(masterplan;masterplan[axp])/CALCULATE(SUM(masterplan[axp]);ALLSELECTED(masterplan)))

where

axp (column in masterplan or masters sheet 2)= masterplan[AVANCEPLAN]* RELATED('masterDatos (2)'[WEIGHT VALUE])

but I have issues to do the running total of this measure acum.

I know it’s a tricky explanation but I expect you to understand.

REGARDS, DANIEL

[v-shex-msft]

Hi @Anonymous,

Can you please share a pbix file with some sample data for test and coding formula?

Regards,

Xiaoxin Sheng