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

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

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!