Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Lexey
Helper I
Helper I

error with VALUES

Colleagues, Daxers need help!
there is a table with the works and cost http://prntscr.com/j5mbxu for each building


There is a planned budget, I do the small analysis on distribution of current costs and I try to impose it on the plan BUT if in the filter I choose one object everything is OK if two

 

Error Message:
MdxScript (Model) (13, 50) Calculation error in measure 'Management_Timesheets'[Values of budget]: a table of multiple values was supplied where a single value was expected. the formula which distributes the interest :


Values of budget = VALUES(Management_Timesheets[Contract amount (EUR)])* [Procentage]

Procentage = [ Honorar]/ CALCULATE([ Honorar]; ALLSELECTED(Management_Timesheets))

 

Ask for help!

1 ACCEPTED SOLUTION

Hi @Lexey,

 

The issue here is your aggregation level, using the all fact table to make your percentage when you add several columns from your table it will give you the total cost divided by all the slice and dices so not 100% per column. Because you are making it at a Jobs level you need to calculated it at that level so you need to change your percentage to:

 

Procentage = CALCULATE([total cost] / CALCULATE([total cost]; ALL('fact'[jobs])))

Other issue is Budget value you are making a sumX of the plan and then multiplying that sumx by the porcentage on SUMX the calculations are made line by line and then summed so you need to calculate for each cost line the percentage value and then do the sum you need to change it to:

 

Values of budget = SUMX(plan;plan[plan] * [Procentage])

See result am PBIX file below:

perentage.gif

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

14 REPLIES 14

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.