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?
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.
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? :)
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!!
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?
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
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)
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)
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!
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 :-)
Why doesn't support Power Pivot this out of the box like Microsoft Power BI does? Thanks for the really helpful video!
An EXCELLENT tuturial - clear, organized, professional - AND - an effective learning tool...cannot ask for more than that !!! Ha !!!
Wow, thanks!
Hello, can you have a "many-to-one" relationship in a power pivot excel file?
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.
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?
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!
Great job. Alas, my head is spinning lol
Hi Steve Sullivan, thanks for the comment!
hahaha.. Same here
Me too
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!
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
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))
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!
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.
What about if you have a date column too?
I couldn't agree more with other positive comments below. This is by far the best explanation video. Thank you so much!!
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.
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
Helped me very much, ty
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?
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?
Sir please help, how about many to many relationship using dates
It helps so much - Gazak Allah Khier - Allah will reward you
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
I am going to give this a shot, it's been the best detailed explanation I found so far!
Can't you just aggregate "prodcat" table (group by category), then merge it with "cust" table on category field ?
thank you so much, it's so helpful and thanks again
Great Video
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.
Hi Doug, what if you don't have ProductID in your Product table, will this method be workable? thank you
Yes
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....
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)
OMG! You've just solved my problem. Love from India ❤️🙏👍
Glad it helped!
Thanks Dough H your video ended my 1 week search on the exact same issue I was having
Glad it helped!
This helped a lot to resolve a problem I have had for many months now!! Thanks!
Glad it helped!
The calculation step solved my problem. Thank you so much!
Hi Antonio Matheus Tinoco, you're welcome!
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 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
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!
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
Sumif formula in column C would be quicker.. ;)
Hi RLprod, thanks for the comment!
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 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!
OMG Awesome man.. you made my day ..! thanks a lot!
You’re welcome!
I read that blog months ago and could not make it work, thank you this! It WORKS!
Thanks john4thelove, glad it helped!
Many...many thanks....to you and the man behind your source- blog!!!
Much appreciated!
Excellent Way of Teaching... Thank You
Thanks and welcome!
Thanks man
You're welcome!
Thank you very much!! very clear explanation!
You are welcome!
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 :)
Beautiful video. Thanks for the share.
Hi Syed Muzammil Mahasan Shahi, thanks for the comment!
thank you so much
You’re welcome!
Thanks in a million. Awesome!
You’re welcome!
Thank you!
You're welcome!
Thanks a lot!!!
You're welcome!
Merci
De rien!