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!

  • lindafly618's avatar
    lindafly618
    Regular Visitor
    8 years ago

    quentin_vigne

    Forgot to @ you in the previous message. Thanks.

  • 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!