Forum Discussion

lindafly618's avatar
lindafly618
Regular Visitor
8 years ago
Solved

Handle data with different granularity within same table

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...
  • quentin_vigne's avatar
    quentin_vigne
    8 years ago

    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

     

     

quentin_vigne's avatar
quentin_vigne
Solution Sage
8 years ago

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's avatar
lindafly618
Regular Visitor
8 years ago

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!