Dax help to bring back the max percentage back in a matrix
I am trying to figure out how to display the maximum hit rate for a boxer per location in a matrix. I wrote the dax calcMaxHitRate, but I know that is pulling back the highest rate for a single row of data, while I am looking for the aggregate of the location. For example, Boxer #2 has a hit rate of 95.4% overall, but their best hit rate is Fresno at 96.7%. My challenge is I figured out how to write the display to show the “Top Gym” (text), but how do I write the dax to show me the 96.7% (because Fresno is the best) for Boxer #2, 96.6% for Boxer #1 (because Toledo is the best) and so on? Link to PBIX here
Average total by day-number of each month for a measured total.
Hi, I have a dataset of starters , with their true start date, and also the date that they paid a deposit. e.g. Start Date Deposit Date Calendar Month Starter 2022-06-02 00:00:00 2022-06-02 05:00:25.7480000 2022-06-01 00:00:00 1 2022-06-02 00:00:00 2022-05-26 09:15:02.4466667 2022-06-01 00:00:00 1 2022-06-02 00:00:00 2022-05-24 08:23:22.0766667 2022-06-01 00:00:00 1 2022-05-26 00:00:00 2022-05-11 14:06:23.9666667 2022-05-01 00:00:00 1 2022-05-26 00:00:00 2022-05-13 12:46:03.2166667 2022-05-01 00:00:00 1 2022-05-26 00:00:00 2022-05-20 09:10:57.9300000 2022-05-01 00:00:00 1 I use the following DAX to calculate, from each date , how many starters are 'known about' in the month from how many deposits are in the system as of that date which also stops data returning from the current date onwards, and accounts for starters on the day into the totals: Starter Total = IF( MAX('dim_DateReference'[Date]) > TODAY(), BLANK(), IF(MAX('dim_DateReference'[Date]) = TODAY(), CALCULATE(COUNT('PBI Starter'[Deposit Date]), ALL('dim_DateReference'[Day Number]), 'PBI Starter'[Deposit Date] <= MAX('dim_DateReference'[Date]) + 1, 'PBI Starter'[Start Date] <= EOMONTH(MAX('dim_DateReference'[Date]),0)) ,CALCULATE(COUNT('PBI Starter'[Deposit Date]), ALL('dim_DateReference'[Day Number]), 'PBI Starter'[Deposit Date] <= MAX('dim_DateReference'[Date]), 'PBI Starter'[Start Date] <= EOMONTH(MAX('dim_DateReference'[Date]),0)) )) I need to calculate, for each day number of the month, on average how many starters are 'known about'. I.E on day one of the month, we have 20 known starters for the month, on average we should know about 24 so we need to improve to meet monthly targets. I also have to plot the average by day against the current month actual. Thanks in advance.
Average of Rates
I built out a table in powerBI to give me average of a series or rates. However, the average is wrong. I wrote out the new table like this: summarizecolumns(Sheet1[District],'Sheet1'[Fiscal Year],"Usage Rate",Average('Sheet1'[%Beds])) For example, district only has one hospital with a rate of 81.1% for a specific year. However, when I create the table for with "Usage Rate" by district for that specific year, the table reads 83.5. The table should have a rate fo 81.1 for one hospital in that district for that year . Since that average is wrong, I can safely assume the average is wrong for all other districts with multiple hospitals.
