Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have Store Id's, month and sales$ for 2019. This is my raw data. I want to Know how many of the storeId's fall in the ranges i mentioned below in the second picture.
I want the result like below. (This is number of stores that fall under the range given, after summing up the sales of all the months of 2019)
How do I do this. Thank you for your response.
Solved! Go to Solution.
Hi @Anonymous ,
Believe that the issue is related with a semi-comma that you didn't replace by a comma. Try the following:
DoorCountUnique = COUNTROWS ( FILTER ( SUMMARIZE ( ALL ( 'DC-R TV'[UpdateMemberNum] ), 'DC-R TV'[UpdateMemberNum], "Total Sales$", SUM ( 'DC-R TV'[Dollars] ) ), [Total Sales$] <= MAX ( BinSelect[MaxValueRange] ) && [Total Sales$] >= MAX ( BinSelect[MinValueRange] ) ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
Create an unrelated table with the following format:
Sales_Range
ID | Description | Min | Max |
1 | 0-30 | 0 | 30 |
2 | 30-100 | 31 | 100 |
3 | 100-200 | 101 | 200 |
4 | 200-400 | 201 | 400 |
5 | >400 | 401 | 9999999999 |
(I created the higher than 400 because your values don't sum as you have in the split, but this can be change to whatever values you want.
Now create the following measure:
Store_Count_Unique = COUNTROWS ( FILTER ( SUMMARIZE ( ALL ( Sales[Store_ID] ); Sales[Store_ID]; "Total Sales"; SUM ( Sales[Sales] ) ); [Total Sales] <= MAX ( Sales_Range[Max] ) && [Total Sales] >= MAX ( Sales_Range[Min] ) ) )
Result is in the image below and the PBIX file attach.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsALL ( Sales[Store_ID] ); Sales[Store_ID]; "Total Sales"; SUM ( Sales[Sales] )
COuld you please explain the above part in the query.
Is the Sales[Store_ID] = Table[column].
If so what does "Total Sales" in the line represent ?
Hi @Anonymous ,
What I'm doing in the measure I created is a temporary table that makes the sum per store of the sales. In that temporary table I need to name the SUM of the sales and I decided to call it Total Sales.
This virtual table column is then used on the filter part to check if the store should be counted on your ranges or not.
You can call it whatever you want as long as the name is then reused on the filter part of the measure.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
Believe that the issue is related with a semi-comma that you didn't replace by a comma. Try the following:
DoorCountUnique = COUNTROWS ( FILTER ( SUMMARIZE ( ALL ( 'DC-R TV'[UpdateMemberNum] ), 'DC-R TV'[UpdateMemberNum], "Total Sales$", SUM ( 'DC-R TV'[Dollars] ) ), [Total Sales$] <= MAX ( BinSelect[MaxValueRange] ) && [Total Sales$] >= MAX ( BinSelect[MinValueRange] ) ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
You need to create the new measures as I refered on previous response and then place it on the values of the matrix setup.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis is great ! Thanks a Ton. I was able to add percentage of door count as well.
How do we add, the total sales number to this. I mean in that range what is the exact number of sales did it make?
and the average sale that each store made between the range.
Could you please help me with the below.
How can we add, the total sales number to this. I mean in that range what is the exact number of sales did it make?
and the average sale that each store made between the range.
Hi @Anonymous
Try the following code:
Sales_Total = IF ( HASONEFILTER ( BinSelect[Description] ), SUMX ( COUNTROWS ( FILTER ( SUMMARIZE ( ALL ( 'DC-R TV'[UpdateMemberNum] ), 'DC-R TV'[UpdateMemberNum], "Total Sales$", SUM ( 'DC-R TV'[Dollars] ) ), [Total Sales$] <= MAX ( BinSelect[MaxValueRange] ) && [Total Sales$] >= MAX ( BinSelect[MinValueRange] ) ) ), [Total Sales$] ), SUM ( Sales[Sales$] ) )
Tried to adjust the formula to the names of your table based on the previous measure you send out.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |