Hello! I am new here, so hopefully I have put my question in the proper spot and what I’m asking makes sense.
I have a report that was created using queries from two universes (merging on an identifying number) because I needed to pull in a column of information that was not available in one of the universes. This column provides details per identifying number such as apple, orange, lime, grape, etc. I’m running into an issue when the identifying numbers have two or more of the fruits listed. Is there a way to show all of the fruits associated with the identifying number instead of #MULTIVALUE? I did try using the “Avoid duplicate row aggregation” but I’m including spend totals in this report and I want those aggregated, not a row for each invoice amount.
A detail item expects a 1:1 relationship with its owning dimension so your solution would not be appropriate.
OP, you are creating your own fan trap with this
=Max([Invoice Amount]) in ([ID Number]) would get you the invoice amount for each ID.
Another option would be to equally apportion the invoice amount across the number of fruit in a given ID, along the lines of:
=[Invoice Amount]/Count([Fruit Name]) in ([ID Number])
If you had £1,200 for an ID and three fruit, the first would show £1,200 for each but total correctly, while the second formula would show £400 per row and total correctly. Your solution should be driven by the display requirement.
(BOB member since 2017-12-20)