So, high-level theory question: I have a dashboard which shows some stats for our Affiliates. A given Affiliate can either give us business directly from their own clients, which will be tied to the Affiliate's account, or they can give us business by way of referring a new account to us, who will have their own account.
For this dashboard, I want to have three views: One which shows business directly provided by the Affiliate, a Second which shows business only from the new Accounts referred by the Affiliate, and a third view which combines these two numbers together. In order to save on the number of redundant measures and visuals I must create, I created a Calculation Group with three Calculation Items, which simply take advantage of the USERELATIONSHIP function to change the relationship between my Sales table and the Account table to calculate the First and Second views, and then a third Calculation Item which calculates each metric for both relationships and then adds the result together.
This works perfectly for any measure which can be summed together (i.e. SUMs, COUNTS, etc.) but, obviously, does not work at all for ratio based measures (i.e. DIVIDEs, AVERAGEs, etc).
So, my question is: is there a way for me to take advantage of Calculation Items such that for summative measures, it just calculates the measure twice, once for each relationship, and then adds them together, while for ratio measures it adds the divisor and dividend together for each relationship first and then performs the division?
I.e. for a Sales Amount measure, I would add CALCULATE(SUM(Sales[grossrevenue], USERELATIONSHIP(Sales[accountid], Accounts[accountid]) + CALCULATE(SUM(Sales[grossrevenue], USERELATIONSHIP(Sales[referredby_accountid], Accounts[accountid]).
Whereas for an Average Sales Amount measure I would want the Calculation Item to modify the measure from DIVIDE(SUM(Sales[grossrevenue]), SUM(Sales[quantitysold]))
to:
DIVIDE(CALCULATE(SUM(Sales[grossrevenue]), USERELATIONSHIP(Sales[accountid], Accounts[accountid])) + CALCULATE(SUM(Sales[grossrevenue]), USERELATIONSHIP(Sales[referredby_accountid], Accounts[accountid])),
CALCULATE(SUM(Sales[quantitysold]), USERELATIONSHIP(Sales[accountid], Accounts[accountid]))
+ CALCULATE(SUM(Sales[quantitysold]), USERELATIONSHIP(Sales[referredby_accountid], Accounts[accountid]))
Is this a thing that is even possible, or am I expecting too much out of Calculation Items? If need be I can just create three separate sets of measure, one for the Direct Affiliate business, one for the Affiliate Referral business, and one for both combined but... I don't want to.
