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

     

     

lindafly618's avatar
lindafly618
Regular Visitor
8 years ago

quentin_vigne Hi Quentin,

 

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

Thanks!

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!