Comparing Values from One Column to Another Sumarraizing

[Anonymous]

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.

I have a table of bi annual sales data for different franchises:

What I would like to do, is create a measure (or series of measures) that help me determine how many Franchises (denoted by franchiseID) beat the regional average sales.  So, something like IF: Franchise Sales >= Regional Average sales, Return 1, Count all return positives to get total number.

I have tried some variation of Summarize, and I can get the number of sales entires for each date period and for each franchise, but I'm struggling to find the DAX procedure to allo wme to compare values against each other. 

Should I just create a calculated column? Create a SUMMARIZE measure?  Help!

So grateful for the expertise this site provides.

[Ashish_Mathur]

Hi,

Your data is vague.  If the sale of Franchise ID 1 for 12/15/2018 was 54 (instead of 654), then would that Franchise ID be counted?  That same Franchise ID on 06/15/2018 reported a Franchise sale > Average sale.  On the date that you have shared, show the expected result very clearly.

[amitchandak · Accepted solution]

@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]

Thank you thank you thank you!

@amitchandak 

[Anonymous]

@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.