I have the following problem to figure it out...
I have a table with sales data and I need to count the number of rows in a table and group them by state.
For example, State X had 100 sales and State Y had 125 sales. Then, I have to divide the number of days with sales in the current month for each state. Let's say State X had sales on 10 days and State Y had sales on 11 days. So, for State X, I calculate 100 divided by 10, which gives me 10. For State Y, I calculate 125 divided by 11, resulting in approximately 11.36.
Next, I need to multiply these results by the total number of days in the current month to estimate the month-end sales (not amount but sales count). However, the total number of days in the month can vary based on the state. For instance, State X might have had a state holiday, while State Y did not. So, for State X, I multiply 10 by 29 (assuming 29 days in the month), resulting in 290. For State Y, I multiply 11.36 by 30, yielding approximately 340.
To obtain the total month-end estimate, I should sum these values: 290 for State X and 340 for State Y, giving me a total of 630.
However, the matrix I'm using to aggregate the month-end estimate assumes a different calculation. It assumes 225 sales (the total for both states) divided by 11 (the total count of sales days, ignoring the breakdown by state) and then multiplied by 30 (ignoring the varying days in the month per state). This calculation results in 613.
If I include the "summarize" function to group the data by state, it will simply multiply the 613 by 2 since there are two states, resulting in 1226.
Unfortunately, I have no idea how to resolve this issue.
This is the formula
Est Sales =
VAR daysSold = DISTINCTCOUNT(SalesReport[Date Sold])
VAR daysinMonth = CALCULATE(MAXX,'Sales Projection 2023','Sales Projection 2023'[days]),GROUPBY'Sales Projection 2023','Sales Projection 2023'[state]))
VAR sales = COUNT(SalesReport[sales_id])
VAR estimate = DIVIDE(sales,daysSold)*daysinMonth
RETURN
SUMX(SUMMARIZE(Entity,Entity[state]),estimate))
NOTE:
- The entity table has a one-to-many relationship with the sales report table.
- If a remove summarize, it still not adding the count of rows per state correctly.
