Forum Discussion

refint650's avatar
refint650
Helper III
2 years ago
Solved

Total Average cost

Hello All

 

how can i write expression for calculatin grand total average cost for all products.

 

this expression didnt work, any recommendation what should i change.

var PCount = calculate( distinctcount( Productcount), all(products))

Var Pamount = calculate( sum(netamount), all(product[year]))

return  calculate (divide( pamount,pcount,0), all(product))

thanks

RF

Interesting Links
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, refint650 

    Based on the data provided by your image, I used the following sample data:

    If you want to ask for the average value of the total amount, you can try the following DAX expression:

    MEASURE =
VAR _total_amount =
    CALCULATE ( SUM ( 'Table'[Amount] ), ALL ( 'Table' ) )
VAR _Count =
    COUNTROWS ( DISTINCT ( ALL ( 'Table' ) ) )
RETURN
    DIVIDE ( _total_amount, _Count )

    This expression _total_amount sum all amounts, ignore all external filters (because the all function is used), _Count this function will count the table distinctly. The final result is as follows:

    I've provided the PBIX used this time below. If there is anything you don't understand, please let me know and I will do my best to answer your questions.

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, refint650 

    Based on the data provided by your image, I used the following sample data:

    If you want to ask for the average value of the total amount, you can try the following DAX expression:

    MEASURE =
VAR _total_amount =
    CALCULATE ( SUM ( 'Table'[Amount] ), ALL ( 'Table' ) )
VAR _Count =
    COUNTROWS ( DISTINCT ( ALL ( 'Table' ) ) )
RETURN
    DIVIDE ( _total_amount, _Count )

    This expression _total_amount sum all amounts, ignore all external filters (because the all function is used), _Count this function will count the table distinctly. The final result is as follows:

    I've provided the PBIX used this time below. If there is anything you don't understand, please let me know and I will do my best to answer your questions.

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • refint650's avatar
      refint650
      Helper III
      2 years ago

      where do we have option to attach Pbi file to post.?

    • refint650's avatar
      refint650
      Helper III
      2 years ago

      Anonymous 

       

      Appreciate for sharing solution and it did work, how do i attach sample data & PBI.?

       

      Thank  you 

      Rf

  • Irwan's avatar
    Irwan
    Super User
    2 years ago

    Hello refint650 

     

    please check if this accomodate your need.

     

    Total Average Cost of Product =
    var _TotalProductCount = CALCULATE(DISTINCTCOUNT('Table'[Product Type]),ALL('Table'))
    Var _SumAmmount = CALCULATE(SUM('Table'[Ammount]),ALL('Table'))
    Return
    DIVIDE(_SumAmmount,_TotalProductCount)
     
    your DAX looks good but maybe you can change couple things:
    - use table as the value inside ALL since you want to measure everything in table
    - maybe no need ALL function in divide since you have calculate whole table in previous measures
     
    Hope this will help you.
    Thank you.