error with VALUES

[Lexey]

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!

[MFelix]

Hi @Lexey,

Without better information is difficult to get your error, but from the Error message and the measures I'm pointing to Values of Budget you are getting the VALUES and multiplying it by a percentage this will give you error since for a measure you need to a single value based on context like the measure is you are getting all the values and not giving it any context.

If you want to calculate a single value per contract maybe you should try a SUMX for your calculations that will make an interaction per each row of your tables.

However in order to help you better you must provide some sample data and expected result, the screenshot is not elucidative and neither are your measures regarding the model and the setup you have.

Regards,

MFelix

[Lexey]

I prepared the file. https://drive.google.com/open?id=1NfAeo4P2d9axo-0znBtYJ1bU_skKJWP7
The problem 1-distribute the planning budget in the ratio as it is now distributed running costs, if you choose one building. All expenses of one building is 100%, the distribution between works as a percentage.

Problem 2-when I select two buildings in the slicer, the total percentage of 100% starts to be distributed between the two buildings 46% and 64% . How to fix 100% of each building. 

need help

[MFelix · 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:

Regards,

MFelix

[Lexey]

Thank you very much! At first I was glad, but the joy was short-lived, after I moved the measures on my data, why it did not work. Probably because in the original table each work can be repeated more than once, there is no more difference. I still need help.

[MFelix]

Hi @Lexey,

What is the full extent of your data in terms of te pivot table? What are the columns you are using to get the final visual only Jobs and buildings or something else also?

Regards,

MFelix

[Lexey]

A strange thing was with PBI! I certainly copied this table from another page and for some reason it caught a cache, repeated everything on the new page and everything worked as it should!

thanks again for your help

[MFelix]

Hi @Lexey,

Nice to hear please mark the correct answer so that other can be helped also.

Regards,

MFelix

[Lexey]

ок.

one more question, please. my work is divided into subtypes. if I choose one of the types on a separate slicer, then on the filtered table I get 100% for this group of works and also 100% of the budget. How can I fix the initially calculated percentage and the value of the budget. Thanks

[MFelix]

@Lexey,

Can you please elaborate a little better on this? What is the setup between the subtypes and the works.

Regards,

MFelix

[Lexey]

As the air need help

[Lexey]

thanks, figured it out, there was a problem with my slicer.

But my task is complicated. If It is not difficult for you, you could help me once again. I don't understand why I lose a row context if values of budget is a row context. I'm trying to calculate the remaining amount to the plan in % and, as I understand, have a filter context.

http://prntscr.com/j6ycr3

[MFelix]

Hi @Lexey,

Be aware that adding a column with the sum of the budget is not giving context to your measures, you need to remake this based on the values you have.

Can you share the formula you use for the delta%,

Regards,

MFelix

[Lexey]

 Hello   @MFelix !  Unfortunately, I was moving in the wrong direction, so I cannot get the desired result.

I need to take a 45 percent budget allocation for the reference building, put that percentage on the plan of another building, and compare it to actual costs to see a deviation from the standard on costs.

Help me please add a measure to each building which will be the percentage of building number 45 [% 45] . I can't bring it if you have chosen a filter on another building

https://drive.google.com/file/d/1h0s3Dunq0liw8BKbpDK0PjeujpncBHgQ/view?usp=sharing

[Lexey]

Коллегам, Даксеру нужна помощь!
есть таблица с работами и стоимость  http://prntscr.com/j5mbxu для каждого здания

Существует запланированный бюджет, я делаю небольшой анализ распределения текущих расходов, и я пытаюсь навязать его по плану, но если в фильтре я выбираю один объект, все в порядке, если два

Сообщение об ошибке:
MdxScript (модель) (13, 50) Ошибка вычисления в измерении 'Management_Timesheets' [Значения бюджета]: была представлена ​​таблица с несколькими значениями, где ожидалось одно значение.

формулу, которая распределяет проценты:
Значения бюджета = VALUES (Management_Timesheets [Сумма контракта (EUR)]) * [Процент]

Процент = [Honorar] / РАСЧЕТ ([Honorar]; ALLSELECTED (Management_Timesheets))

Попросить помощи!