I can't thank you enough. I often needed some way to combine results by a time period or market segment and this solution is so easy and well illustrated. You have made my Friday. Thank you.
This is solid! Great job on explaining DAX cross table filtering. I’d been spinning wheels more times than I can dare to count. I am now going to dazzle the masses, thanks to this!
Btw, on close scrutiny, I don’t think you need that extra “unique customer” filter table. The unique product bridge table s/b sufficient to pull the correct totals. It puzzled me the first time but still great example!
you probably dont care but does anyone know a method to log back into an Instagram account..? I somehow lost the login password. I appreciate any help you can give me
Agreed. Is there any solution to get the Grand Total to sum the parts? My thought was to add this new table to the data model too, but Power Pivot doesn't allow that.
Great solution for the dilemma of many to many relationships. My example is similar but the values repeated are percentages and I am not solving the problem with a sum formula (adding percentage is not correct). Any idea on a work around?
Hi, i follow the tutorial the calculation in potentialSales row is correct but the grand total didn't calculate true it's like show the maximum value, do you know what wrong?
Agreed. Is there any solution to get the Grand Total to sum the parts? My thought was to add this new table to the data model too, but Power Pivot doesn't allow that.
Great job! One question. Same example as yours, but a DATE instead of a PRICE (so no sum or counts in “Values”) - namely, a product order date for a specific customer. Would like to show all the orders to be placed in a date. Doing so, I would like to put the date at second column (after “Customer” but before “product” in your example). Unfortunately, it seems that the date is not linked to the product to be ordered (all the dates are present even though, from raw data, no orders should be present all the days). Thanks for your help! Marco
Doug H it seems doesn’t work unfortunately - I have created a relationship between order dates present in the table “prod” and a new table (“calendar table”). It continues showing all dates present in the calendar table (no sense) and then the product associated with the customer. Thanks for the help
@@marcolaz92 I also have similar situation. I was able to show number of dates within a month, quarter, etc. Totals were still wrong, but all within ranges was OK. But had to use blank so that it was not counted.. Could not take it one step further ... to map categories for groups.
Hi Doug, I applied this solution to a sample I have. I have a Product table where the price is, a Customer table where I have many repetitive elements, and I have 6 tables for unique elements. Each of these tables with unique tables are connected to the Customer table (addressing the one to many relationships) and I also have a connection between the Product table and one of the unique tables (addressing the one to many relationship). I created a measure "xIRR" and the formula works. I created a pivot table showing the xIRR by the different elements but it only works for 2 out of the 6 tables with unique elements. Did I provided enough info to diagnosed why it is not working for the rest of the elements?
Great video! I'm working on a problem where I need to add another dimension to the measure formula to calculate a proportion. Essentially, CALCULATE( (sum of variable A in table 1) / (count of variable B in table 2). I get the error: "Calculation error in measure 'NameofMeasure': A function 'COUNT' has been used in a True/False expression that is used as a table filter expression. This is not allowed". Any suggestions? :)
This video was very helpful, and it does seem to be working for my case. I'm trying to organize my data from a main table using some groups that have a many to many relationship. However, I keep getting an error message that says: "A Pivot Table report cannot overlap another PivotTable report". Any idea what is causing that?
Hi MountainSnowFlyer, thanks for the comment. Basically the update to one of the pivot tables in a worksheet will extend the column/rows onto another pivot table on the sheet, so a solution is to move the pivot tables away from each other (i.e., to another range or another worksheet)
Yes you would need to manually update. The other option would be to create a query on the cust table to pull out the unique customer names and then output that as the uniquecust table and then use it as part of the bridging table...that might be another video :-)
I'm still getting errors.... Will this concept work with setting up a relationship in access and then importing into power pivot?.... I'm now at 7 tables and still can't get referential integrity between them (so it can be a clean import into power pivot)
Jesus titty fucking christ. This is an absolute game changer. I've struggled with this for days and I finally managed to make it work. Thanks alot for the video!!
Man, you are the best
really
i spent so much time to figure it out
and you here just in 10 min explain everything ...
Thanks OV MOV, glad it helped!
I am going to give this a shot, it's been the best detailed explanation I found so far!
I couldn't agree more with other positive comments below. This is by far the best explanation video. Thank you so much!!
I can't thank you enough. I often needed some way to combine results by a time period or market segment and this solution is so easy and well illustrated. You have made my Friday. Thank you.
Thanks Derek Garner, glad it helped!
The calculation step solved my problem. Thank you so much!
Hi Antonio Matheus Tinoco, you're welcome!
Helped me very much, ty
You’re welcome!
This is solid! Great job on explaining DAX cross table filtering. I’d been spinning wheels more times than I can dare to count. I am now going to dazzle the masses, thanks to this!
Glad it was helpful!
Btw, on close scrutiny, I don’t think you need that extra “unique customer” filter table. The unique product bridge table s/b sufficient to pull the correct totals. It puzzled me the first time but still great example!
Finally!! After searching for a very long time I finally stumbled on this video. You’ve increased my education bigtime with this one. Appreciate it.
Glad it was helpful!
you probably dont care but does anyone know a method to log back into an Instagram account..?
I somehow lost the login password. I appreciate any help you can give me
An EXCELLENT tuturial - clear, organized, professional - AND - an effective learning tool...cannot ask for more than that !!! Ha !!!
Wow, thanks!
Excellent video, I have been beating my head over how to do this for the last 2 days, you saved me a lot of headache.
Glad it helped
This helped a lot to resolve a problem I have had for many months now!! Thanks!
Glad it helped!
OMG! You've just solved my problem. Love from India ❤️🙏👍
Glad it helped!
Why doesn't support Power Pivot this out of the box like Microsoft Power BI does? Thanks for the really helpful video!
Thanks Dough H your video ended my 1 week search on the exact same issue I was having
Glad it helped!
It helps so much - Gazak Allah Khier - Allah will reward you
Excellent Way of Teaching... Thank You
Thanks and welcome!
This video hit the spot! great job!! however grand total of potential sales isn't accurate. how do you spin that? it has me stuck on me current work.
Agreed. Is there any solution to get the Grand Total to sum the parts? My thought was to add this new table to the data model too, but Power Pivot doesn't allow that.
Many...many thanks....to you and the man behind your source- blog!!!
Much appreciated!
I read that blog months ago and could not make it work, thank you this! It WORKS!
Thanks john4thelove, glad it helped!
Great solution for the dilemma of many to many relationships. My example is similar but the values repeated are percentages and I am not solving the problem with a sum formula (adding percentage is not correct). Any idea on a work around?
thank you so much, it's so helpful and thanks again
Great Video
Can't thank you enough! Been suffering with this issue for so long, and I'm so thankful to have found your help. Like & subscribe..Done!
Thanks soloseven007, glad it helped!
Thank you very much!! very clear explanation!
You are welcome!
Looks good, could you also get a list of the products under each category in the resulting report?
Suppose so, depending on how the pivot is built out
OMG Awesome man.. you made my day ..! thanks a lot!
You’re welcome!
Thank you for the video. When applying slicers for let's say customer name, based on what table in the data model would you do this?
Thanks in a million. Awesome!
You’re welcome!
When you add this kind of tables for a many-many relationships it can be considered a star model or is a different kind?Thank you
thank you so much for these videos!
How to solve one to one relationship model in powerpivot, please?
Sorry, don’t understand the comment/ question... but it sounds more like a lookup item? ruclips.net/video/fzfN84kDqgU/видео.html
Beautiful video. Thanks for the share.
Hi Syed Muzammil Mahasan Shahi, thanks for the comment!
Thank you!
You're welcome!
Thanks man
You're welcome!
thank you so much
You’re welcome!
Hi, i follow the tutorial the calculation in potentialSales row is correct but the grand total didn't calculate true it's like show the maximum value, do you know what wrong?
Agreed. Is there any solution to get the Grand Total to sum the parts? My thought was to add this new table to the data model too, but Power Pivot doesn't allow that.
Great job!
One question. Same example as yours, but a DATE instead of a PRICE (so no sum or counts in “Values”) - namely, a product order date for a specific customer.
Would like to show all the orders to be placed in a date.
Doing so, I would like to put the date at second column (after “Customer” but before “product” in your example).
Unfortunately, it seems that the date is not linked to the product to be ordered (all the dates are present even though, from raw data, no orders should be present all the days).
Thanks for your help!
Marco
The concept would be similar but a calendar table will be the bridging table between the two.
Doug H it seems doesn’t work unfortunately - I have created a relationship between order dates present in the table “prod” and a new table (“calendar table”).
It continues showing all dates present in the calendar table (no sense) and then the product associated with the customer.
Thanks for the help
@@marcolaz92 I also have similar situation. I was able to show number of dates within a month, quarter, etc. Totals were still wrong, but all within ranges was OK. But had to use blank so that it was not counted.. Could not take it one step further ... to map categories for groups.
Great job. Alas, my head is spinning lol
Hi Steve Sullivan, thanks for the comment!
hahaha.. Same here
Me too
Thanks a lot!!!
You're welcome!
Hi Doug, what if you don't have ProductID in your Product table, will this method be workable? thank you
Yes
Hi Doug, I applied this solution to a sample I have. I have a Product table where the price is, a Customer table where I have many repetitive elements, and I have 6 tables for unique elements. Each of these tables with unique tables are connected to the Customer table (addressing the one to many relationships) and I also have a connection between the Product table and one of the unique tables (addressing the one to many relationship). I created a measure "xIRR" and the formula works. I created a pivot table showing the xIRR by the different elements but it only works for 2 out of the 6 tables with unique elements. Did I provided enough info to diagnosed why it is not working for the rest of the elements?
Hello, can you have a "many-to-one" relationship in a power pivot excel file?
Great video! I'm working on a problem where I need to add another dimension to the measure formula to calculate a proportion. Essentially, CALCULATE( (sum of variable A in table 1) / (count of variable B in table 2). I get the error: "Calculation error in measure 'NameofMeasure': A function 'COUNT' has been used in a True/False expression that is used as a table filter expression. This is not allowed". Any suggestions? :)
Not sure...maybe wrap it in with a FILTER function? CALCULATE( FILTER ( (sum of variable A in table 1) / (count of variable B in table 2))
What about if you have a date column too?
Can you please tell what is the name of picture you used for this video (blonde girl pointing at table)
Purchased a licensed version for this pic and it's called "Database Table - technical concept, girl pointing screen"
@@DougHExcel Thank you very much for reply!
This video was very helpful, and it does seem to be working for my case. I'm trying to organize my data from a main table using some groups that have a many to many relationship. However, I keep getting an error message that says: "A Pivot Table report cannot overlap another PivotTable report". Any idea what is causing that?
Hi MountainSnowFlyer, thanks for the comment. Basically the update to one of the pivot tables in a worksheet will extend the column/rows onto another pivot table on the sheet, so a solution is to move the pivot tables away from each other (i.e., to another range or another worksheet)
What happens when a new customer is added to the “cust” table...do you need to manually update the “uniquecust” table as well?
Yes you would need to manually update. The other option would be to create a query on the cust table to pull out the unique customer names and then output that as the uniquecust table and then use it as part of the bridging table...that might be another video :-)
@@DougHExcel There must be a way to update the unique tables automatically.
You can create a power query that saves the distinct results to a table :)
When I do this Excel creates a Connection tied to the filename. If I create a copy the new file breaks when updating. Do you know how to solve this?
Sorry, don’t understand the comment/ question... maybe make a post to the mrexcel.com forum.
Merci
De rien!
I'm still getting errors.... Will this concept work with setting up a relationship in access and then importing into power pivot?.... I'm now at 7 tables and still can't get referential integrity between them (so it can be a clean import into power pivot)
haven't tested this with Access....
Sir please help, how about many to many relationship using dates
Can't you just aggregate "prodcat" table (group by category), then merge it with "cust" table on category field ?
what if you have many field columns with many to many between 2 or 3 tables
that would be additional bridge table, but it's not ideal
Jesus titty fucking christ. This is an absolute game changer. I've struggled with this for days and I finally managed to make it work. Thanks alot for the video!!
Oh my😆 Glad it helped!
Sumif formula in column C would be quicker.. ;)
Hi RLprod, thanks for the comment!