Forum Discussion

Lexey's avatar
Lexey
Helper I
8 years ago
Solved

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 co...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    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's avatar
Lexey
Helper I
8 years ago

ок.

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's avatar
MFelix
Super User
8 years ago

Lexey,

 

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

 

Regards,

MFelix

  • Lexey's avatar
    Lexey
    Helper I
    8 years ago

    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

  • Lexey's avatar
    Lexey
    Helper I
    8 years ago

    As the air need help

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    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's avatar
    Lexey
    Helper I
    8 years ago

     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