Handle data with different granularity within same table

[lindafly618]

Hi everyone,

I'm rather new to Power BI and I'm tasked with following request. The data is something like this:

CampaignID	Flag	Code	Revenue	Cost
Camp1	Acq	Business	$10	$1 (Camp1_Cost)
Camp1	CD	Consumer	$20	$1 (Camp1_Cost)
Camp2	Acq	Business	$30	$2 (Camp2_Cost)
Camp2	Acq	Consumer	$40	$2 (Camp2_Cost)
Camp2	CD	Business	$50	$2 (Camp2_Cost)
Camp2	CD	Consumer	$60	$2 (Camp2_Cost)

 
In this dataset, the revenue of a campaign is broken down into flag & code level. For example, total revenue of Camp1 is 10+20. However, the granularity of cost is on the campaign level. In the example here, Camp1 has 2 records because of different flag & code, and they have different revenue. However its total cost of $1 (campaign level) repeats twice. For the same reason, Camp2's total cost of $2 repeats 4 times. 

The request is to visualize ROI (revenue/cost) and the visualization will respond to user selections of flag & code. My problem is, since cost and revenue have different granularity, I don't know how to sum them properly.

The desired behavior is:
If user filters Flag=='Acq', the result should be (10 + 30 + 40) / (1 + 2). As you can see, the numerator (revenue) is sum of all revenue in filtered rows, but the denominator (cost) is just the sum of campaign level cost, the repeated Camp2_Cost should not be calculated twice.
You might wonder why ROI is calculated this way... since it's a fraction of revenue divided by total cost. I had the same doubt and double confirmed the ask, this is what they want.

I've been stuck in this for days and tried multiple solutions, and still can't seem to figure this out. Any help would be much appreciated!!!

Thanks!!!

[quentin_vigne · Accepted solution]

Hello @lindafly618

Here is the solution : 

1 - Create those measures (right click - add measure)

Measure = MAXX(DISTINCT('Table'[CampaignID]);MAX('Table'[Cost]))

SumCost = SUMX(DISTINCT('Table'[Flag]);[Measure])

SUMRevenue = SUM('Table'[Revenue])

ROI = [SUMRevenue]/[SumCost]

2 - Add a slicer with Flag selection

3 - Add a Card Box or wathever you want for ROI

Here is the result : 

Have a good day 

- Quentin

[lindafly618]

@quentin_vigne Hi Quentin,

Could you please take a look at my follow up question? Appricate your help! 

Thanks!

[quentin_vigne]

Hi @lindafly618

I was on week-end, sorry

So you want user to filter ROI by your column 'Code' ? 

You cant add other value to Distinct() because the Distinct code return a single column table of the selected value

[lindafly618]

@quentin_vigne

Hi Quentin, 

Thanks for your reply! I tweaked your solution a little bit and I was able to get the desired outcome. Instead of this

SumCost = SUMX(DISTINCT('Table'[Flag]);[Measure])

 I changed it to:

SumCost = SUMX(DISTINCT('Table'[CampaignID]);[Measure])

Thanks for pointing me to the right direction, really appreciate it!

[lindafly618]

@quentin_vigne

Forgot to @ you in the previous message. Thanks.

[lindafly618]

Hi Quentin,

Thanks for your reponse! However I have a follow up question - I actually have multiple flag columns, not just the Acq/CD flag. For example, the Business/Consumer column is another Flag column that needs to respond to user selection. Think of these columns as Flag1, flag2, flag3, etc., each has 2 to 3 different categories.  In your solution, in the expression below, I essentially want to add more columns inside the DISTINCT(), because the end user wants to filter on these different "flags" at the same time. 

SumCost = SUMX(DISTINCT('Table'[Flag]);[Measure])

  Looks like PowerBI does not allow for more than 1 column inside the DISTINCT argument, how should I resolve this?

Thank you!