locked
display local cost from USD for budget data RRS feed

  • Question

  • Hi,

    I have the data model from the sample dataset as described in the image.The relationship goes like below:

    • Users to Usage-> 1..M
    • USage to lookup->M..1
    • Lookup to Budget->1..M

    The real dataset contains ~6000 users and 10 more products.There is another table for currency conversion that states local curreny and exchange rates.Each product has a different condition- some are all employee and some are from specific sources.In the sample,i have presented only small sample.

    I want to be able to show the local currency $ as well along with USDcost$ when sliced by Users dimension as presented in the expected output format.I am stuck for the local currency cost and USDCost .USDCost per product is good but when sliced by dept and country,it doesnt roll up correctly.I have 2 measures called :

    HC=

    CALCULATE(DISTINCTCOUNT('Usage'[User]))
    USDCost=
    CALCULATE(sum(Budget[UnitCost])*[HC],USERELATIONSHIP('Usage'[Product],Lookup[Product]))
    Budget[UnitCost] column is calculated as 

    DIVIDE(Budget[PerMonthCost$],[HC],BLANK())


    Wednesday, August 5, 2020 2:09 PM

All replies

  • It seems you are working on a calculated table not a measure.

    You could use a dax query to calculate the cost roll up to country - department level.


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 6, 2020 8:07 AM
  • Can you pls show and guide.
    Thursday, August 6, 2020 11:19 AM
  • I am actually stuck and dont have a good performing model currently.Hence pls guide as what is the best to achieve the USDCost.Here is the sample model :

    Sample

    Sunday, August 9, 2020 2:12 AM
  • If this is a power bi model, you could use the built-in analyzer to record and view the performance, see which part is cause issue: Power BI Performance Analyzer 

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Friday, August 14, 2020 5:35 AM