Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amid
Regular Visitor

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 7
Anonymous
Not 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

 
 

@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
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

amid
Regular Visitor

I try to continue the DAX formula with TREATAS to create a virtual relationship.
And TREATAS returns the correct result 35 rows

https://prnt.sc/ql5y34

 

But then, something goes wrong ...  698 rows

https://prnt.sc/ql5zoe

amid
Regular 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

https://prnt.sc/ql6hmt

 

amid
Regular Visitor

🤔 very very slow ... need to look for another solution .. 

amid
Regular Visitor

Screenshot_1.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.