cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User I
Super User I

Is this Possible? DAX many to many

Okay, this one is a tough one for me but is probably very simple for some of the DAX gurus on this page.

I have two fact tables: Campaigns and Sales.  Normally I use one item table for the sales facts that is based upon item number and then associates manufacturer and product catagory. The campaign table has both of those listings  but not an item number which means it is a many to many connection which doesn't work too well. In order to calculate totals, I broke the item table into two pieces: Manufacturer and Product. Some campaigns have a manufacturer but not a product and some have a product and not a manufacturer. I would like to use this data to apply a weighted amount to manufacturers for product campaigns that do not have a manufacturer. I have given these generic names in order to build a foundation. What I don't know how to do is to bring the two look ups together. I would like to divide the product spend by the number of manufacturers for that product and assign it to the manufacturer to add to manufacturer specific totals.

Sample data:

Campaign Spend:

CampaignTotal Spend
Caps125
Gowns230
Shoes551
Dresses321
A625
B741
C525
D112

 

Manufacturer

Manufacturers Mfg Specific spend
A625
B741
C525
D112

 

Product weighted

ProductsManufacturerWeighted Spend
CapsA62.5
CapsC62.5
GownsB76.66
GownsC76.66
GownsD76.66
ShoesA137.75
ShoesB137.75
ShoesC137.75
ShoesD137.75
DressesC160.5
DressesD160.5

 

Desired Outcome

ManufacturerTotal Spend
A825.25
B955.41
C962.41
D486.91




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Is this Possible? DAX many to many

I ended up flattening this report through a series of other reports which I then imported as a dataset. It does require a series of refreshes instead of a single but it works.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
Highlighted
Helper I
Helper I

Re: Is this Possible? DAX many to many

maybe its late , maybe i've not quite understood what you were trying to get at but you can get your desired outcome like this

ts:=CALCULATE(SUM(Manufacturer[Mfg Specific spend]))+CALCULATE(SUM('Product weighted'[Weighted Spend]),FILTER(CROSSJOIN('Product weighted',Manufacturer),'Product weighted'[Manufacturer]=Manufacturer[Manufacturers]))

-- ------------------------------ --
Please mark solutions as complete when your question has been answered
-- ------------------------------ --
Highlighted
Community Champion
Community Champion

Re: Is this Possible? DAX many to many

@kcantor I believe you can do this with Summarize again Smiley Happy

 

I tested with your data then added some additional refreshed and it seems to work...

But then again I don't know if this would work for you... Let me know.

SUMMARIZE 2.png

Highlighted
Super User I
Super User I

Re: Is this Possible? DAX many to many

@Sean

 

This doesn't seem to be helping me as my data is more complex than this simple set of example data. Can I send you  an actual sample?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Champion
Community Champion

Re: Is this Possible? DAX many to many

@kcantor I just had the feeling it would not Smiley Sad - it was just too easy...

 

As far resources I think we all learn from Marco and Alberto's Books and very helpful site

and same applies to Rob Collie's Books and website - which I've seen you recommend too Smiley Happy

Highlighted
Super User I
Super User I

Re: Is this Possible? DAX many to many

I do use those books and sites as well. I have messaged you a copy of the data. Hopefully you can guide me to what I am doing wrong.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User I
Super User I

Re: Is this Possible? DAX many to many

So, what I have done . .

I added a calculation to determine how many manufacturers exist within a catagory. I added a calculated column to determine if an ad group had a related manufacturer. I created a calculation dividing the sum of ad group costs where "had mfg" = 0 to determine how much to add to each mfg within a catagory. Now I just have to figure out how to apply that amount automatically across the board.

messy

cumbersome

ugly

 

Any ideas?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User I
Super User I

Re: Is this Possible? DAX many to many

Win a shirt . . . solve my problem to earn points. 🙂

In other words  "BUMP"





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User I
Super User I

Re: Is this Possible? DAX many to many

I ended up flattening this report through a series of other reports which I then imported as a dataset. It does require a series of refreshes instead of a single but it works.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Community Champion
Community Champion

Re: Is this Possible? DAX many to many

Glad you got it working! This data set was a nightmare - blanks, negative values in what seemed like every field in every table Smiley Happy

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors