In Search of an Efficient Approach for Grouping and Analyzing Measures
Hey everyone! I will now present the scenario I have, and I need your help to see how you would approach it. I have “solved” it in an incorrect way because my solution consumes too many resources and is inefficient I have 5 measures, measure1, measure2 … measure5. These measures visually need to be grouped (as a visual separation without totaling) into two groups: Group 1: measure1 …3 Group 2: measure4, measure5 For each measure, I have: Budget, Real, Budget LY, Real LY, Budget YTD, Real YTD, Budget LYTD, Real LYTD. I have 2 companies: Company 1, Company 2. The measures were developed before the existence of calculation groups (but I don’t know if the solution involves calculation groups). Now I will show you the grid structure I am looking to visualize. I need your help on how you would approach the problem. Thanks in advance.
Total for Top grouping in a matrix not calculating correctly
Hi, I wonder if someone can help me with the DAX for a Measure. I have a target field in a Matrix that doesn't seem to be calculating correctly. See below In the first level grouping the values are correct, but not in the top grouping or for the grand total. The Target is a running total for the month grouped by Program, so should be the sum of all the Target values in the top level group and for the grand total. The DAX I'm using is fairly simple. Uses 2 measures. [TargetWholeNumber] = SUMX(DISTINCT(DimDate[MonthName]), FIRSTNONBLANK(MeasureData[Target], 0)) This is the measure that I'm using in the matrix. [TargetWholeNumberRunningTotal] = CALCULATE([TargetWholeNumber], DATESYTD(DimDate[DateValue], "30/06")) Any advise will be appreciated. Cheers,
grouping the Measures
Hi There, I want to know how can group my Measure Values as below I have made success Rate % by using that DAX code, It generates Success Rate with percentages So I need to group those percentages in to groups as 0 - 25 % 25- 50 % 50 - 80 % 80 - 100 % Appreciate if someone can help to categorize the measure
Target Sales Percentage based on Store ID in DAX
Hello All, I have the below data set for sales for which I am trying to get a percentage of sales in measure. Store ID Sales Date Daily sales Total Employees 101 1/1/2021 5 9 101 1/1/2021 8 9 101 1/1/2021 10 9 102 1/1/2021 7 11 102 1/1/2021 5 11 102 1/1/2021 11 11 102 1/1/2021 8 11 102 1/1/2021 12 11 I want to have Perctenage KPI which calculates Store Total Sales / Total Employees. For example, for Store 101 my measure will return (5+8+10)/9 = 2.55 so on and so forth... By default, I would like to have a value that returns data for all stores. In above example. All the sales will be summed (66) and then divided by (9+11) = 20 and the result should show as 3.3 Any guidance will be helpful. Thanks,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.
Creating a dynamic table based on on-screen filters
I would like to measure to what extend items are available for a certain project within certain types of storage locations. I’ve got two fact tables (‘Required items per project’ and ‘Available items’) and two filter tables (‘Item’ and ‘Location’). For example, P1 requires 25x item 001 and there are 25 items available (100%) organization-wide. However, as our users would like to be able to filter on several specific combinations of locations to deliver items, dynamic filters are required. In case of only counting the available material with type of storage set at User (locations A,B, D, F) for project P1, the graphs should show 17/25 = 68% availability. So far, we’ve only succeeded in doing this with predetermined filters and merged tables in the powerquery. But as our list of locations is far longer than 6 and doing this with merged tables would result in an enormous amount of columns, we would like to see if it’s possible to use a filter on the board’s main screen for this operation. Could you please help us with the required steps to achieve this? We're now looking at the following steps: - Create dynamic table, based on on-screen location filter; - Create SUM measures (or column) for availabilty per item, based on aforementioned dynamic table; - Calculate availability (Available items / required items). Kind regards, Stephan
Creating groups based on scenario value outcomes
Hi there, I hope you can help!! As an output, I need a table which counts the number of hours of an activity, against the size of the activity (grouped) e.g. 0-9 30.00 10-19 2.00 As a benchmark for scenario planning I have done this by creating bins, and am using a measure to sum up as appropriate. However, I need to create scenarios. There are two scenarios within the same output: (1) Scenario value increases or decreases the number of total hours but the size remains the same - this I have managed using the same bins as in the benchmark. The actual duration calc is based on a total number of attendees/size of the group = no. of events to run * duration. This is the scenario where we expect multiple of the same event to run. (2) Where there is only one event running, the scenario value applies to the number of attendees instead - the duration will not fluctuate I need to then combine these outputs into the one table as above, so we can see the change in hours (resultant from scen1), and the change in distribution across the size bands (resultant from scen2(. We don't need a distinction between scen 1 and 2 in the final table. The problem is that the outputs from scenario (2) are still based on the original event size and not on the scenario size output, and I don't know how to group them so it recognises the new value rather than the original. Hope that makes sense - appreciate any helps as this is driving me mad!
