Forum Discussion
Calculate Sales by Price Type
Hello
I have a calculated table. It show the amount of sales by type of price. I do not know how to leave rows with values in this calculated table and delete empty ones. Please, help. Maybe someone knows how to do it
7 Replies
- amidRegular Visitor
- AnonymousNot applicable
Amid,
The trick is to create a relationship that links the 2 tables you have for sales and for price by date. The best option here is to relate them by the date. Then you can write a simple measure that just takes the number of sales multiplied by the price of the object on that given date.
https://drive.google.com/file/d/1rhZBL6K7fH5YwcJqY6_w82b4dSP0xwtC/view?usp=sharing- amidRegular Visitor
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$] )- amidRegular Visitor
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