cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sy898661 Member
Member

Overall Average and Mean of multiple tables

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 😞

 

1 ACCEPTED SOLUTION

Accepted Solutions
sy898661 Member
Member

Re: Overall Average and Mean of multiple tables

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!)

Capture.PNG

View solution in original post

2 REPLIES 2
Highlighted
Community Support Team
Community Support Team

Re: Overall Average and Mean of multiple tables

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
sy898661 Member
Member

Re: Overall Average and Mean of multiple tables

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!)

Capture.PNG

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors