Forum Discussion
Hi All,
I am struggling to get the below solved using dax, could someone help plss
We have date and time dimensions with sales for every 15 minute intervals. I have a measure for sum of sales, I would like to know the count of sales greater than 40,000 for a given date and area. (example a date has 10 sales where its greater than 40,000 at different intervals on that day)
I have tried the COUNTROWS but it is only counting for distinct dates.
Try a measure like this:
Count Sales Over 40k = VAR vSalesByDateAndArea = ADDCOLUMNS ( SUMMARIZE ( FactTable, FactTable[Date], FactTable[Area] ), "@Sales", [Sum of Sales] ) VAR vResult = COUNTROWS ( FILTER ( vSalesByDateAndArea, [@Sales] > 40000 ) ) RETURN vResult
3 Replies
Try a measure like this:
Count Sales Over 40k = VAR vSalesByDateAndArea = ADDCOLUMNS ( SUMMARIZE ( FactTable, FactTable[Date], FactTable[Area] ), "@Sales", [Sum of Sales] ) VAR vResult = COUNTROWS ( FILTER ( vSalesByDateAndArea, [@Sales] > 40000 ) ) RETURN vResultThanks alot DataInsights , this is working but it doesn't calculate for some of the dates as those sales for the dates are coming from a different fact table (based on a condition, if the sales from fact 1 is 0 then use sales from fact 2), I just realised that there are two fact tables. is there a way for this?
(we are using a live model with limited access to the model).
So the requirement is to check if there are any sales more than 40000 and if not then check in fact table 2 for that date / time / region, if there is then use the sales from facttable2. and count the rows. The existing measure for sum of sales captures the same logic (if sales blank in fact 1 then use values in fact 2)
Hi DataInsights, I have unioined the both fact table rows and getting the desired results.
Thank you so much for the help
