Forum Discussion

wilson_smyth's avatar
wilson_smyth
Icon for Post Patron rankPost Patron
7 years ago
Solved

Order total on each order line, when using dimensions

Im trying to calculate the order total at an order line level.

If i just use one giant fact table, this is easy, but once i use dimension tables, the order total stops working.

 

Ive tried variations of ALL, ALLSELECTED, CALCULATETABLE but none seem to help.


it seems like a standard problem to face, but it does not see that easy to resolve.

Note, i have not enabled bi-directional relationships, as my actual model has a few fact tables and is complex enough already without introducing ambiguity.

 

Woudl appreciate a pointer in the correct direction.

 

The sample problem is located in a pbix here.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI wilson_smyth,

     

    I 'd like to suggest you use following measure to display total order on order line total level:

    Total Order Price =
CALCULATE (
    SUM ( Sheet3[price] ),
    ALLSELECTED ( Sheet3 ),
    VALUES ( Sheet3[OrderLineID] ),
    VALUES ( Sheet3[Supplier] )
)

     

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI wilson_smyth,

     

    I 'd like to suggest you use following measure to display total order on order line total level:

    Total Order Price =
CALCULATE (
    SUM ( Sheet3[price] ),
    ALLSELECTED ( Sheet3 ),
    VALUES ( Sheet3[OrderLineID] ),
    VALUES ( Sheet3[Supplier] )
)

     

    Regards,

    Xiaoxin Sheng

    • wilson_smyth's avatar
      wilson_smyth
      Icon for Post Patron rankPost Patron
      7 years ago

      Thanks Anonymous that works!
      Would you mind explaining how this works as its not clear to me why values() works but allselected() does not.