Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Table = FILTER( ADDCOLUMNS( DISTINCT( CROSSJOIN( FILTER( VALUES('Stores[Store]), 'Stores'[Store] <> BLANK() ),
FILTER( VALUES('SKU'[SKU]), 'SKU'[SKU] <> BLANK() ),
FILTER(VALUES(Dates[WE]), Dates[WE] >= TODAY()-(365) &&
Dates[WE] < TODAY() ) ) ),
"Sales", CALCULATE( SUM('Sales'[Qty]),
'Sales'[PO #] <> 0,
'Sales'[bolInvoiceSent] = TRUE() ) ),
[Sales] > 0 )
Here is what I currently have to build my Measure table that I want to use for calculating Average Monthly Sales based on a rolling 12 month sales history, but I would rather filter only where the Sales is 0 if there was no previous sales yet, but to actually include the 0 if in a week following a prior week that had sales.
Then I want to take the sum of those sales and divide by the DistinctCount of the total WeekEndings (WE). That total will then be multiplied by 52 to make a year and divided back down by 12 to determine a rough monthly average.
So the help I am looking for is first, how do I only filter the 0 sales if no prior weeks had sales but still retain where 0 sales occured after any previous weeks did contain sales, then second how to get the DistinctCount to work based on this table? The DistinctCount only works on a ColumnName but doesn't allow you to start typing any measure to try and tell it what to count without referencing a physical table's Column which defeats the purpose of why I want to do the DistinctCount within this created measure table.
Hi @AFoulk,
From your description, I could not figure out your data structure, could you please post me more pictures about your data information or share your pbix file if possible?
Regards,
Daniel He
Hi Daniel,
What exactly do you mean by data structure?
Hi @AFoulk,
Could you please post me the sample data in your different tables or share the pbix file if possible?
Regards,
Daniel He
I make a small sample explaining what I want a DAX measure to calculate for Average Sales.
How do I attach it onto this post?
FILTER( ADDCOLUMNS( DISTINCT( CROSSJOIN( FILTER( VALUES('Stores[Store]), 'Stores[Store] <> BLANK() ),
FILTER( VALUES('SKU'[SKU]), 'SKU'[SKU] <> BLANK() ) ) ),
"Mnthly Avg Sales", CALCULATE( SUM('Sales'[Qty]), 'Sales'[PO #] <> 0,
'Sales'[bolInvoiceSent] = TRUE(),
'Sales'[WE] >= TODAY() - 365,
'Sales'[WE] < TODAY() +1 )
/ DISTINCTCOUNT( 'Sales'[WE])
* 52
/12),
[Mnthly Avg Sales] > 0 )
Now I tried this and figured I would just reference the actual table for my results measure as typed below, but then the results populating onto my table/matrix are all the same total (not breaking down by product per branch servicing the stores). I have the SUMX calculating in a parameter for % change in sales to manually increase/decrease these needs total.
SUMX('Store Avg Monthly Sales', 'Store Avg Monthly Sales'[Mnthly Avg Sales] * (1 + 'Needs'[Needs Change Value] ))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |