Implement Aggregate Sum column based on values from other columns
Hello All I wish to implement a column D as shown in below screenshot using DAX measure such that Column D aggregation is sum of values in Column C. For Example, Value in Row number D3 is sum of values from Rows C3 to C8 such that it is aggregated on same supplier value from Column A from row A3 to A8 and the sum in row D3 must also correspond to MDF in row B3 with highest value in row C3. Is this possible to achieve in Power BI table visual using DAX ??
Aggregate Measure correctly by condition on DATEDIFF
I have the following Table: It represents processes with a certain category. And there is also a Date Table over column TIMESTAMP. I would like to show a Measure based on another Measure that calculates the Date-Difference until the selected Date. So first this is how I calculate the Date-Difference: AGE = VAR SELECTED_DATE = CALCULATE(MAX(DATUM[Date]), ALLSELECTED(DATUM)) VAR STARTDATE_PROCESS = Calculate(MAX(Workflow[MIN_TIMESTAMP]),DATUM[Date]<=MAX(DATUM[Date]), ALL(DATUM[Date])) RETURN DATEDIFF(STARTDATE_PROCESS,SELECTED_DATE,DAY) Now I want to use a Measure which depends on the result of AGE, like NEW = IF([AGE]<=3,CALCULATE(COUNT(Workflow[PROCESS]),ALL(DATUM))) or OLD = IF([AGE]>3,CALCULATE(COUNT(Workflow[PROCESS]),ALL(DATUM))) That works so far, but the aggregation is not working correctly as you can see here, there should be a 3 on NEW and OLD aggregated: Any idea how to fix that? Thank you!Unwanted Aggregation with dynamic Ranking
Hi, I am trying to create a dynamic ranking measure. Lets say I have n rows after every filter operation. Now I want the rows enumerated from 1.. n, according to their original id (which is unique). link to pbix <- here is a reproduction of the error. Password: Example%5 I tried the solution by @Eirc_Zhang from this post but it does not work for me. Here is the code how I modiefied it, to use the index as the parameter for ranking. RankID = MINX ( FILTER ( SELECTCOLUMNS ( ALLSELECTED ( ParentData ); "index"; ParentData[Index]; "rank"; RANKX ( ALLSELECTED ( ParentData ); ParentData[Index];; ASC; DENSE ) ); [index] = MAX ( ParentData[Index] ) ); [rank] ) Everytime I use a visual with non unique field combinations, the rows are aggregated, as you can see in Visual (1) in my posted image. In Visual (2) the unique field "Index" is added and then there is no aggregation. The final goal would be to use a non unique field and the RankID togehter in a scatter plot (Visual 3). Since you can't add there another field there would be always an unwanted aggregation. Can someone help me to fix the problem? (I already managed to do it with a python visual, but I would rather have an interactive visual. I kinda wish, I could use python to calculate measures ) I actually don't fully understand the linked solution and would appreciate, if someone could help me to understand it a little better. For example I have a hard time to understand how the MINX function works, since the inner filter call just returns a table with a single row as output, as you can see in the next image. Best regards, Andreas
Correct Total using ALL() whilst also having blanks filtered out of visual?
Hi I'm hoping someone may have a quick solution to a problem i'm encountering whilst trying to provide % of total in a bar chart. The screenshot below shows a dummy dataset for customer feedback replicating the issue i'm encountering. The data spans over 3 days (20th Oct to 22 Oct), with a total of 25 customers, 9 of which opted not to give feedback. I'm needing to present this data as a % broken out by feedback score. I have used the 'feedback%' measure shown below, which leverages the ALL() function to clear the feedback_rating filter, providing a total customer count. You can see from the 2nd bar chart that this works when the blank feedback rating option is included in the visual. Example feedback score of 1 has 5 customer entries, 5/25 = 20%. I have been asked to remove blanks from the visual. When these are removed however, the % are incorrect as can be seen within the red bar chart, a feedback score of 1 now has a % distribution of 26%. The reason appears to be due to the Date slicer which is required in my real dashboard. As all the customers on 21st October did not leave feedback, I believe when the blanks are removed from the visual, all of these rows are removed from the underlying data, creating a new customer total of 19, resulting in incorrect %'s, feedback score of 1 now becoming 26% (5/19) as mentioned above. Any ideas on how to keep these values in the total whilst also keeping the blank entries out of the visual? Ideally I would like to solve it within a DAX measure as my existing dashboard already has a relatively complex data model, however I appreciate it may require a helper table of some sort. feedback% = distinctcount(Sheet1[customer_id]) / CALCULATE(DISTINCTCOUNT(Sheet1[customer_id]),ALL(Sheet1[feedback_rating])) Thanks for reading.
Measures to aggregate by month & range values
Hi all, My data table is something like: Im asked to get a table/histogram with a row/bar per month and the number of IDs per range of summatory of hours/month: Here is an intermediate table to help you guys understand the requirement: Is it possible to get it with just with measures/DAX and not having to create the intermediate tale in the data model?? Thanks a lot in advance!
Dynamic Agregation - hardest so far
Hey guys, I am struggling with one of the hardest mind games I have encountered in DAX so far. I have a data set that can be pivoted to cohort table like this: Sum of EndingMRR YearsActive Row Labels 0 1 2 3 4 5 2020-01 8,000 7,000 7,000 6,800 7,800 7,500 2020-02 16,000 15,000 13,000 12,000 12,000 2020-03 5,000 4,300 4,000 4,000 2020-04 7,000 7,500 7,500 2020-05 14,000 14,100 Grand Total 50,000 47,900 31,500 22,800 19,800 7,500 I would need to divide MRR from each active year with their base from zero year - i.e. I would need to get retention % of all those customers that are active for a certain number of years. (for example numbers in orange: for 4th year that would be 19,800 divided with only the sum of orange numbers from zero years that is 24,000 and I would get 82.5%; for 5th year that would be 7,500 divided by only 8,000 from zero year etc) I have tried to do it with the following formula = DIVIDE ( SUM ( CustomerData[EndingMRR] ), CALCULATE ( SUM ( CustomerData[EndingMRR] ), ( CustomerData[YearsActive] = "0" ) ) ) but this is wrong because it is taking the sum of all the numbers from zero year instead of only specific ones. Using excel to show you the logic and the outcome: Please help! Any advice is welcome. I appreciate your help guys! Thank you!
