Hi so I have a visual that contains three value fields that are measures. Revenue, cost and result. The legend is set to a grouping that contains three groups, one for our first office and one for our second office. The third one is the "other" group, this group vill contain thoses posts in out bookkeeping that cannot be directly linked to any office.
I have been instructed to deal with this byt distributing their total value 60%/40% between the offices.
I tried to create new measures that would be hold "Office One Revenue", "Office Two Revenue", "Office one Cost"... and so forth. This would give me complete control by combing measures that contained the sum of all posts that could be identified with an office and add them with a precentage of those who could not. This gave me an acceptable visual. The problem was that this decoupled the visual from the original revenue, cost and result measures. So with that method I cannot drill down in the same way as I would from our other visuals but would have to build a page for each of these six new measure where we could drill down.
I also tried to do this with groups but couldn't.
Is there a way to solve this problem? Ideally I would like to be able to do it trough something similar to a grouping so the values for the visual are the three mentioned measures...
I'd try to do something in Power Query - duplicate your existing query a couple of times and hack out anything in the new versions that's already assigned to your two offices, then hack out anything in your original query that's undefined. Then in your two new queries, you can try reducing the values of everything to 40% in one and 60% in another, and then change whatever field you use for your office grouping so that they relate to office 1 and office 2 as appropriate, then merge everything back together?