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

Trouble Calculating Sum / Countrow

Hello All,

   Having trouble wrapping my head around how to get the number I'm looking for here. Here's the scenario (note I thought about posting some sanitized data to help, but it's millions of rows, multi tables....so I hope my explanation here suffices).

 

First, the relationships:

Label is related to the mrProducts table on a many-to-one relationship.  (Labels * : 1 mrProducts)

OrderItems table is the sales table, related to mrProducts on a many-to-one relationship (OrderItems * : 1 mrProducts)

OrderItems relates to Campaigns as many-to-one (OrderItems * : 1 Campaigns)

 

Campaigns relates to Blocks as one-to-many (Campaigns 1 : * Blocks)

Blocks relates to Codes as one-to-many (Blocks 1 : * Codes)

 

 

relationship_diagram.png

 

We issue codes used to validate against a product sale. Many codes are issued, only a percentage are redeemed. I'd like to figure out how many codes - on a per label basis - are issued (and from there I can figure out the percentage). The report canvas has a slicer with Labels. Selecting that label shows all the sales no problem, but trying to establish how many codes were issued per campaign is where I get stuck.  The Codes table has all codes that are issued.  Essentially, I need to count the number of rows in Codes that are related to any of the products within the Label found set.  That's a very circuitous route:  Codes -> Blocks -> Campaigns -> OrderItems -> mrProducts -> Label.

 

Any thoughts? Is it possible? Let me know if I can further simplify this explanation. 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Trouble Calculating Sum / Countrow

hi,@a68tbird 

  After my research, you may try to change Cross filter direction to both when creating the relationship

between Campaigns and OrderItem.

if it is not your case, please share your demo pbix file the expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
a68tbird Member
Member

Re: Trouble Calculating Sum / Countrow

Thanks Lin. What would my calculation be then? Could you help with that?