Measure to Calculate % Complete Across Four Different Tables

[gemcityzach]

Hey folks,

I have four different tables of data. Each of them represents a piece of a business process. All four stages of the business process will eventually reach 100%. For each table, I can easily create a Measure to calculate an overall %-complete because the users have to traverse the length of each table/process and complete the work. I.e., if there are 1000 rows, 1000 rows have a Status of 'Incomplete' and eventually they will all be flagged as 'Complete'. This is true across all four tables. I have a calculated column in each table that looks at various fields in a given table to determine if it is complete and returns a 0 (incomplete) or 1 (complete). Therefore, to create a %-complete for a given table I would just create a measure on that calculated column.

However, I need to be able to report 'Overall % Complete', which considers the status of all four business processes so I can inform management how the entire process is proceeding. How best could I do this?

To throw anoter wrinkle at you 🙂 Within each table there are Business Units and Review Names. I'd like to be able to create summary tables that show the overall status by each Business Unit and/or Reviewer. Is it as simple as a pivot on those fields and tossing the same measures in?

[v-tianyich-msft]

Hi @gemcityzach ,

I made simple samples and you can check the results below. I modeled two tables with similar structure, created a new append query, removed unnecessary columns and then performed calculations on this table:

Total = var _total = CALCULATE(COUNTROWS('Append1'),ALLEXCEPT(Append1,'Append1'[Item]))
var _Completed = CALCULATE(COUNT('Append1'[ID]),FILTER('Append1',[Completed]=1))
RETURN DIVIDE(_Completed,_total)

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[gemcityzach]

Hey Scott I think I see why it's not working for me but does work for you. You're aggregating on ID. That is not a field that I have that is common between all four tables. All four tables have different values that specify their uniqueness. There is no relationship between these four tables other than the fact that they are part of an end-to-end business process.

Table 1 'unique ID' is called RP_ID of the form '834143'

Table 2 'unique ID' is called OR_IR of the form '1234'

Table 3 'unique ID' is called CS_ID of the form 'AAAA-AA-AA-000000000000'

Table 4 'unique ID' is called PS_ID of the form 'AAAA-0000000'

[v-tianyich-msft]

Hi @gemcityzach ,

I'm not aggregating ID, My ID are just to make each row a separate piece of data, the important ones are "A", "B" and they are grouped as categories.can you provide your sample data?

Best regards,
Community Support Team_ Scott Chang

[gemcityzach]

Hey Scott thanks for your response. Did you do this work primarily in PowerQuery? You appended all of the tables together into a new table and then you can do measures on a single table and use filters if you need to parse out by group/item, etc?

[v-tianyich-msft]

Hi @gemcityzach ,

No, I just did an append operation only. In your data model, you would append the four tables, then delete the other unnecessary columns, keeping only the category and "progress", and then do the above calculation.

Best regards,
Community Support Team_ Scott Chang

[gemcityzach]

I'm not sure that will work for my situation. Each table (SharePoint list) is a different length and they are not of the same structure. They are from four stages within the same business process (end to end).

I'm also not sure what 1, 2 and 3 get me. Those are the overarching stages I am already doing to load and transform the data.

My question is asking for guidance on a specific type of analytic measure (compute % complete across four tables) of the prepared data.

[jmarciogsousa]

If possible merge (union) the bussiness tables in one Fact table.

For the fact table, if possible prepare it asap:
1. On the source/database, example excel, SQL, spark...

2. PowerQuery 
3. Dax