Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Single Value Error

Hi All,

 

I have two data sets as per the below examples;

 

Task IDHire Duration (Days)
110
2100
312
45

 

Task IDHire Cost (£)
110
2100
3700
41000

 

Please note 'Hire Cost (£)' and 'Hire Duration Days' are both calculated columns from other columns within each table.  

 

I want to display a card visual which shows average hire cost (Hire Cost * Hire Duration). At the moment I am using the following DAX;

 

Total Hire Cost = VALUES(Job_card_detail_tbl[Daily Hire Cost]) * VALUES('Standing Scaffold Data'[Hire Duration]) / COUNTA(Job_card_detail_tbl[TaskId])
 
When I display this measure if the Card visual I receive the below error;
 
'A table of multiple values was supplied where a single value was expected.'
 
I also have a map which shows the location of each task id and I wouldn't mind having a tooltip which states each individual ID's hire cost (hire cost * hire duration). I have tried this but receive the same error.
 
Any ideas on how to resolve this?
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Fowmy's avatar
    Fowmy
    6 years ago

    Anonymous 

    You can use the same Measure I shared or use the following. The difference is when you have one ID the results from both are same but in times of multiple IDs, the prior Measure with Average X will average and later will sum.

    Hire Cost = 
SUMX(
    Job_card_detail_tbl,
    Job_card_detail_tbl[Hire Cost (£)] * 
    LOOKUPVALUE(
        'Standing Scaffold Data'[Hire Duration (Days)],
        'Standing Scaffold Data'[Task ID],
        Job_card_detail_tbl[Task ID]
    )
)

     

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon on the right if you like this reply 🙂

    YouTube, LinkedIn

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    Anonymous 

    Use the following measure to get the average cost

    Total Hire Cost = 
AVERAGEX(
    Job_card_detail_tbl,
    Job_card_detail_tbl[Hire Cost (£)] * 
    LOOKUPVALUE(
        'Standing Scaffold Data'[Hire Duration (Days)],
        'Standing Scaffold Data'[Task ID],
        Job_card_detail_tbl[Task ID]
    )
)



    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon on the right if you like this reply 🙂

    YouTube, LinkedIn

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Fowmy ,

       

      Thanks for your response, that has worked. Do you have a solution for enabling me to see individual hire cost (hire cost * hire duration) that can used on a tooltip for my map visual?

       

      Thanks,

       

      E

      • Fowmy's avatar
        Fowmy
        Super User
        6 years ago

        Anonymous 

        You can use the same Measure I shared or use the following. The difference is when you have one ID the results from both are same but in times of multiple IDs, the prior Measure with Average X will average and later will sum.

        Hire Cost = 
SUMX(
    Job_card_detail_tbl,
    Job_card_detail_tbl[Hire Cost (£)] * 
    LOOKUPVALUE(
        'Standing Scaffold Data'[Hire Duration (Days)],
        'Standing Scaffold Data'[Task ID],
        Job_card_detail_tbl[Task ID]
    )
)

         

        ________________________

        Did I answer your question? Mark this post as a solution, this will help others!.

        Click on the Thumbs-Up icon on the right if you like this reply 🙂

        YouTube, LinkedIn

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    6 years ago

    You can try following 

     

     

    _Avg = AverageX(Values(Job_card_detail_tbl[TaskId]), SUM(Job_card_detail_tbl[Daily Hire Cost]) * SUM('Standing Scaffold Data'[Hire Duration]))

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous ,

    Create a new task id dimension


    Task = distinct(union (all('Standing Scaffold Data'[Task ID]), all(Job_card_detail_tbl[Task ID])))

     

    and measure like

     

    AverageX(summarize(Task,Task[Task ID],Sum(Job_card_detail_tbl[Daily Hire Cost]) * Sum('Standing Scaffold Data'[Hire Duration]), "_2" COUNTA(Job_card_detail_tbl[TaskId])),divide([_1],[_2]))