Forum Discussion

amid's avatar
amid
Regular Visitor
6 years ago

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

 

download *pbix example

 

 

7 Replies

    • amid's avatar
      amid
      Regular 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 t

      But 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$] )
      • amid's avatar
        amid
        Regular 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