Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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))
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.
@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))
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |