Forum Discussion

Roman_Zalesskii's avatar
3 years ago
Solved

Unit price measure

Hi, need you help.

I have a table that looks something like this:

Dateorder №Type of productCollectionPriceAmount
1/10/2022OFR-001Door SlabA3002
1/10/2022OFR-001Door SlabB2401
1/10/2022OFR-001Hardware 3023
1/11/2022OFR-002Door SlabC4101
1/11/2022OFR-002Hardware 605
1/11/2022OFR-003Door SlabA3103
1/11/2022OFR-003Hardware 4060
1/11/2022OFR-004Door SlabA3151
1/11/2022OFR-004Door SlabB2432

 

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.

 

 

3 Replies

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

    • Roman_Zalesskii's avatar
      Roman_Zalesskii
      Icon for Helper I rankHelper I

      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.