Aggregating over date in a measure
Hello, I want to build a measure which shows the number of days people login to their account per month, quarter, year. The data is in this format - with user ID, login date. I then create end of month and end of quarter in query view and create "Days with a login (month)" and "Days with a login (quarter) as calculated columns using the DAX below. Days with a login (month) = CALCULATE ( DISTINCTCOUNT ( 'Login activity'[Login date] ), ALLEXCEPT ( 'Login activity', 'Login activity'[User Id], 'Login activity'[End of Month] ) ) Days with a login (quarter) = CALCULATE ( DISTINCTCOUNT ( 'Login activity'[Login date] ), ALLEXCEPT ( 'Login activity', 'Login activity'[User Id], 'Login activity'[End of Quarter] ) ) I then do a distinct count of user ID to build the visuals below. Can someone help me with the dax to build the measures which allow me to build the above visuals but without the need for multiple visuals and calculated columns? So I want to use the dates hierarchy from my dates table on the x-axis (so I can drill up and down) and then build measures for number of people who have logged in on 1 day, 2 days, 3 days etc. So I will have multiple measures which work using my dates table and no calculated columns. Thank you in advance đŸ™‚
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.
How to add totals from chart together down X axis
Hello I have a chart of total sales by date difference. I want to aggregate across the x axis as it moves to the left. For example, date 0 has 155, and date 1 has 108. So I would want date one to show the combined amount of 263 (155+108). This would continue across the chart and increase. How would I do that with dax?
Normal Distribution on Aggregated Values using Average for Last 5 Years
Hi All, I am trying to replicate an Exel formula based on Normal Distribution as a Measure in Power BI. In Excel this formula is used on aggregated values of Year and Region. In Power BI I want the formula to work on the unaggregated dataset to avoid having to create and work off a new table. The formula is as follows: NORMDIST(X, avg(last 5 yrs), SD (last 5 yrs) * 1.5) * 100 Where X is the value I want to see where it sits on the distribution. The data (simplified) is as follows: Record No. Category Allocation Approval Year Region 1 A 1 Yes 2020 South 1 B 1 Yes 2020 South 2 C 1 No 2020 North 2 D 1 No 2020 North 3 A 1 No 2022 West 3 D 1 No 2022 West 3 E 1 No 2022 West Records can be assigned multiple categories and these have been pivoted to show one category per row. Because of this the Allocation column is always 1. I would like the equation to be a single measure so I can easily switch between this and the sum of allocation/approval measures I already have. I would want the output to look something like this: Year A B C D E 2020 > 43 56 48 71 28 South 28 56 45 24 58 North 68 15 47 49 57 West 57 58 25 47 85 2021 62 50 48 69 42 2022 56 47 32 58 45 Thanks in advance!Aggregated values directly at the last rows of TABLE visuals
Hi, Being a newbie requesting help to get the aggregated values below the "total" row generated from TABLE visual. Unable to use MATRIX visual as i need to show the aggregated values below the total(sample representation as below).Will use the same for Date wise as well as month wise ,year wise slicer. Data Set Sample as below: Also i have created Aggregated Table using Power query which is available as a data set: Also have created measures on values for each measuing point separately which is used for other graphical visuals. Deeply appreciate forums support to meet the objective. Thank you.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!
