Overall Average and Mean of multiple tables

[sy898661]

Hi!!

I was wondering if there was a way to rewrite this code or add something in to make it work:

I have a few tables with the following info for different products:

• Lot #
• Start Date
• End Date
• Duration (DATEDIFF of Start and End Dates)

I have another table that is:

• CALENDAR(DATE(2018,1,1), DATE(2020,12,31)
• and Month bins (Jan 2018, Feb 2018, ... December 2020)

The relationship between the different product tables and Month table is connected via End Date

So anyways, I wanted to use the following formula to bring in the Duration for each product into this month table so I can figure out the overall Average and Mean per month (for my visuals)

=SELECTCOLUMNS(RELATEDTABLE(Product 1), "Product 1", Product 1[Duration])

This worked for most of the products, but some products have lots that ended on the same day, so DAX is saying "A table of multiple values was supplied where a single value was expected".

Is there a workaround for this?

So far I have tried:

• Attempt 1
  • Added index #s to each product table
    • Product 1 = 1-100
    • Product 2 = 101-200
    • Product 3 = 201-300
  • Create new table with index column numbered 1-300
  • Relationships to product table via Index # so I can do the SELECTCOLUMNS formula and bring in the Durations that way
  • This is where I hit a roadblock: PBI wouldn't allow relationships to both the index table and the Months table because of "ambiguity" 
• Attempt 2
  • I tried creating multiple index tables (one for each product) and using RELATED brought in Durations
  • Then I tried to append these tables into 1 large table
  • This is where I hit a roadblock: Apparently Power Query Editor does not work with calculated columns... So it appended the Index column but ignored all the Durations
• Attempt 3
  • Create a "Summary" table and have a column for each product's Average
  • Take overall average of these #s
  • This is where I hit a roadblock: it is just one value, one average. I am looking to find a rolling overall average that I can look at month by month

If anyone has any ideas for how I can find this that would be really helpful!!!!!

Thank you! 

P.S. I have created a very basic example file to help show what I am trying to say/do! Here is a link to a Google Drive file upload, please let me know if there are any issues downloading:

https://drive.google.com/file/d/1t0gakGk0e9d1C8BSqaR5xH1hTRnqZUfi/view?usp=sharing

Note: in this example file, each Product table is the exact same. In my real file, the product tables have different # and names of columns so I can't just append them all together 😞

[sy898661 · Accepted solution]

I have found a workaround to solve my problem (FINALLY!!!) 🙂 🙂 🙂

So:

1. I added the index columns into each product table (in Query Editor)
   1. Product 1 = 1-100
   2. Product 2 = 101-200
   3. Product 3 = 201-300
   4. Product 4 = 301-400
   5. Product 5 = 401-500
2. Then I appended the product tables into a new query
3. Then I created a new calculated column for durations using (since some the columns for this were named differently)

   1. IF(AND(Index >=1, Index <= 100), DATEDIFF(Start, End), 
      IF(AND(Index >= 101, Index <= 200), DATEDIFF(Start, End),
      IF(AND(Index >= 201, Index <= 300), DATEDIFF(Start, End),
      IF(AND(Index >= 301, Index <= 400), DATEDIFF(Start, End),
      IF(AND(Index >= 401, Index <= 500), DATEDIFF(Start, End),
      BLANK)))))

4. Then I created a new calculated column for the End Dates (since some the columns for this were named differently)

   1. IF(AND(Index >= 1, Index <= 100), End Date, 
      IF(AND(Index >= 101, Index <= 200), End Date, 
      IF(AND(Index >= 201, Index <= 300), End Date, 
      IF(AND(Index >= 301, Index <= 400), End Date, 
      IF(AND(Index >= 401, Index <= 500), End Date, 
      BLANK)

5. Then I created a relationship for my Months table to the new calculated column

and voila! *chef kiss* a rolling median. (also have a graph for average but same thing only different!)

[v-shex-msft]

HI @sy898661 ,

You can't create a dynamic calculated column/table based on current row contents or filters, it will return static value based on whole table.

Regards,

Xiaoxin Sheng