Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Comparing Values from One Column to Another Sumarraizing

Hello,   I think this should be a straightforward probelm - but I can't figure it out!  I think some combination of variables and SUMMARIZE() will get me there but struggling to put it all together...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , try like


    countx(filter(table[Franchise Sales] >= table[Regional Average sales]),table[ franchiseID])
    sumx(summarize(Table,Table[franchiseID],"_1",sum(table[Franchise Sales]),"_2",sum(table[Regional Average sales])),if([_1]>=[_2],1,0))

amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , try like


countx(filter(table[Franchise Sales] >= table[Regional Average sales]),table[ franchiseID])
sumx(summarize(Table,Table[franchiseID],"_1",sum(table[Franchise Sales]),"_2",sum(table[Regional Average sales])),if([_1]>=[_2],1,0))

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you thank you thank you!

     

    amitchandak 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

       

      I have a quick follow-up question.  Let's say I wanted to determine how many unique franchises beat an arbitrary sales goal in a fiscal year. I'm struggling trying to get the "unique" franchiseIDs using Distinct or Distinct Count.

       

      I'm trying the following:

       

      COUNTROWS(DISTINCT(FILTER('Table','Table'[Franchise Sales] >= 500 && 'Table'[Franchise Sales] <> BLANK())))

       

      This is returning the number of franchises that reported biannual sales of >=500.  But some franchises might have reported >= 500 for both biannual reports. While it's useful to know the total number of reports that had franchise sales >= 500. I want the unique number of franchises that beat that number.

       

      This measure is not getting the expected value.