bins

4 Topics

Counting Items in Group Derived from Ranking
Hi everyone, I have been tasked with producing a 'quintile report'. Given a set of sales data, we want to rank by a sales metric, then group the items into equally sized quintiles. From there, we'd like to count by manufacturer how many of their items appear in each group, and display the result on a 100% stacked bar visual. I have made progress with this by creating a helper table with the quintile minimums and maximum values (0, 0.2, 0.4, etc.). I then used the below dax formula to count / group by the ranking (taking the rank divided by the total items to determine which group it should be in): Item Count by Quintile = CALCULATE(COUNT('Quintile Data'[ITEM]), FILTER( VALUES('Quintile Data'[ITEM]), COUNTROWS( FILTER('Quintile Groups', DIVIDE( RANKX(ALLSELECTED('Quintile Data'[ITEM]),[Dollar Velocity],,DESC), COUNTX(ALLSELECTED('Quintile Data'[ITEM]),COUNT('Quintile Data'[ITEM])),"" ) > 'Quintile Groups'[Min] && DIVIDE( RANKX(ALLSELECTED('Quintile Data'[ITEM]),[Dollar Velocity],,DESC), COUNTX(ALLSELECTED('Quintile Data'[ITEM]),COUNT('Quintile Data'[ITEM])),"" ) <= 'Quintile Groups'[Max] ) ) > 0 ) ) This allows us to split the data in equally sized groups, and it works great if viewing the stacked chart at a total level. However, if I bring the manufacturer into the visual on the x axis, the above calculation appears to be performed at each manufacturer's level. I had initially tried using ALL() instead of ALLSELECTED(), but I do have a filter in the visual (Market) which I need to include in the calculation. My dax skills show their limits when filter contexts get involved, so hoping someone might be able to help with finding a way to include the Market filter but exclude the Manufacturer in the above. Perhaps its a use case for ALLEXECEPT()? I have attached a sample data file and PBIX showing what I'm trying to do. In the PBIX, the bottom chart is an example of the final form, but it is calculating at the manufacturer level currently. Thank you! Sample PBIX Data Sample (CSV)
Solved
ElliottBowles
4 years ago Place DAX Commands and Tips
694Views
0likes
1Comment
Count ID and Chart Occurrence
I want to count how many times an ID shows up in a given time frame and show that as a chart. I created a column that converts the date into the week of the year. What I am trying to show is how many times someone came into the building in a given week. Table_1 ID Date Week 1234 1/1/2022 Week 1 1234 1/2/2022 Week 1 1235 1/1/2022 Week 1 Chat Frequency 1 2 3 4 5 Week 1 1 2 Week 2 Week 3 ….
Solved
Anonymous
4 years ago Place DAX Commands and Tips
1.3KViews
0likes
3Comments
Analyzing sales data by quote size
Hi folks, I must be having a brain fart today because my question seems so basic. I have transactional level sales data that looks like any other sales data - customer names, line item numbers, regional info, sales, discounts, profit, item number, etc. The data are broken out into line item level of detail meaning I have multiple rows of data for each sales order number. All I want to do is analyze my data for sales orders that are below $1,000,000 total value. Then I want to analyze my data for sales orders that are between $1,000,000 and $5,000,000. Lastly I want to analyze my data for sales orders that are greater than $5,000,000. What is the easiest way to do this? I do not need enything exotic. Putting Sales Order# in a filter and filtering the Sales Order# on sum of Total Revenue = "what ever value I need" would do the trick. Thanks!
Solved
Anonymous
5 years ago Place DAX Commands and Tips
2KViews
0likes
4Comments
Change Results on Table with Slider
Hi guys, Hoping that you'll be able to help :). I'm attempting to get a matrix table to show pertentages from measures. I have manged to get these to show, however the percentages are wrong. I managed to get the correct results in a different but these are static results and don't change for all of the data in the report. Ideally I need the pertcentages to change when a slider for a date range is updated. But I'm having a few issuse with this. Can you suggest any ideas? I have been trying to get a summarize to work, but it keeps telling me that there is a bracket out of place somehwere. If a summarize is the wrong thing to use, I am all open to ideas :). I'm not the most fluent with Power BI but any help will be greatly appreciated.
Anonymous
5 years ago Place DAX Commands and Tips
777Views
0likes
1Comment