Forum Discussion
Calculate Sales by Price Type
Anonymous , this is a good solution, but not enough for my case. My problem is to reduce the size of the calculated table Prices per dates. Now her DAX code is as follows:
Prices per dates =
VAR t =
FILTER (
CROSSJOIN (
GROUPBY ( 'Prices', Prices[Product_id], Prices[Date_from], Prices[Date_till], Prices[Price$], Prices[Price_type] ),
'Dates'
),
'Dates'[Date] >= 'Prices'[Date_from]
&& 'Dates'[Date] < 'Prices'[Date_till]
)
RETURN tBut this DAX code now returns an excess of lines. Now my main task, which I can’t solve yet, is to remove these lines at the level of that DAX code.
As a result, the calculated table Prices per dates should not have all the dates from the date range in the Prices table, but only those that are in the Sales table.
I already have a formula for getting the sales amount, it works well. The problem is in the unnecessary rows of the calculated table Prices per dates.
Sales Amount per Prices =
CALCULATE (
[Amount],
TREATAS (
SUMMARIZE (
'Prices per dates',
'Prices per dates'[Date],
'Prices per dates'[Product_id]
),
Sales[Date],
Sales[Product_id]
)
)
* AVERAGE ( 'Prices per dates'[Price$] )clarification
it is necessary that the number of unique combinations of Product_id Date in Sales and Prices per dates be the same. In the example, it should be 35
http://prntscr.com/ql5qm5
- amid6 years agoRegular Visitor
I try to continue the DAX formula with TREATAS to create a virtual relationship.
And TREATAS returns the correct result 35 rowsBut then, something goes wrong ... 698 rows
- amid6 years agoRegular Visitor
The problem turned out to be solved, but not sure about the speed on a large amount of data ..
The question remains open. Can someone tell me another solution
https://prnt.sc/ql6h0t- amid6 years agoRegular Visitor
🤔 very very slow ... need to look for another solution ..