Unit price measure

[Roman_Zalesskii]

Hi, need you help.

I have a table that looks something like this:

Date	order №	Type of product	Collection	Price	Amount
1/10/2022	OFR-001	Door Slab	A	300	2
1/10/2022	OFR-001	Door Slab	B	240	1
1/10/2022	OFR-001	Hardware		30	23
1/11/2022	OFR-002	Door Slab	C	410	1
1/11/2022	OFR-002	Hardware		60	5
1/11/2022	OFR-003	Door Slab	A	310	3
1/11/2022	OFR-003	Hardware		40	60
1/11/2022	OFR-004	Door Slab	A	315	1
1/11/2022	OFR-004	Door Slab	B	243	2

And I'm trying to create a measure that would calculate the unit price for each collection.

Unit price = (Door Slab Price + All Hardware Price)/ Amount of Door Slabs

Because it's impossible to tell for sure for what door slab each hardware has been bought, I need to filter out all orders that include more than one collection and hardware. In ideal, all orders that include more than 1 collection, but consist only of door slabs, should remain. 

From this measure I need to build a line chart and matrix table with dates.

I've created two measures
 This measure intended to calculate the amount of collection in one order. 

Collection Amount=

CALCULATE(

    CALCULATE(

        DISTINCTCOUNT(TDSheet[Collection]),

        TDSheet[Collection]<>BLANK()),

      GROUPBY(TDSheet,TDSheet[order №]),

REMOVEFILTERS(TDSheet[Collection]))

This measure is intended to calculate Unit prices by collection. It works but I have a number of problems with it:

CALCULATE(

DIVIDE(

    CALCULATE(

    SUM('TDSheet'[Price]),FILTER(TDSheet,[Collection Amount]<2)),

CALCULATE(

    SUM('TDSheet'[Amount]),'TDSheet'[Type of product]="DOOR SLAB",FILTER(TDSheet,[Collection Amount]<2))),

    GROUPBY(TDSheet,TDSheet [order №]),

    REMOVEFILTERS (TDSheet [Collection]))

1.It's incredibly slow. Probably because it's filtering from the whole main table. Looks like I need a separate table, not sure how to do it. 

2. Orders that include more than one collection but no hardware (orders with only door slabs, but different collections)  get filtered out. If they stayed i would get more accurate number for door unit.

3. I intended to use it with another measure in line chart, but realised that this measure gets merged in one line and I can't display separate lines for each collection. Instead I get two lines for each measure. So the final measure should include this measure as var:

Average door Unit=

DIVIDE

(CALCULATE(SUM('TDSheet'[Price]), REMOVEFILTERS (TDSheet[Type of product])),

CALCULATE(SUM('TDSheet'[Ammount]),'TDSheet'[Type of product]="DOOR SLAB"))

In the end I need to get this. (The dotted line is for [Average door Unit] Measure)

Still pretty new to dax and power Bi. Any help would be appreciated.

[Roman_Zalesskii · Accepted solution]

All problems solved. Dm if you need a solution. 

[lbendlin]

How is the hardware Amount playing into the scenario?  What would the expected outcome be based on the sample data you provided?

[Roman_Zalesskii]

If we take as an example a table, the outcome would be:

Unit price for the collection C: (410+60)/1 = 470

For collection A: (310+40+315)/4 = 166,25

For B: 243/2 = 121,5

Order #OFR-001 didn't  taken into account because it consists of two models + hardware. 

Hardware amount also wasn't  taken into account. We only need the price of it.