Single Value Error

[Anonymous]

Hi All,

I have two data sets as per the below examples;

Task ID	Hire Duration (Days)
1	10
2	100
3	12
4	5

Task ID	Hire Cost (£)
1	10
2	100
3	700
4	1000

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?

[amitchandak]

@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]))

[FarhanAhmed]

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]))

[Fowmy]

@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]

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 · Accepted solution]

@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