Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Power BI table doesn't sum up

Hi there,

 

I am quiet conserned about an issue i have in my report.

 

I am have built a model X where I want to calculate Budget YTD but unfortunately the table does not SUM UP correctly (Total). In other words, the total per row is correct but the total per column is not correct.

 

I am using SUMX funtion.

 

Budget total is correct but YTD not

I am trying to understand why because I have other columns (Values) in the same table like SalesAmount and Variance but the total for these is correct.

 

The formulas I am using to calculate Budget YTD:

 

Spoiler
Budget YTD = ([Revenue Target] / (([Contract Uren All]) - [Uren Vrij]) * [Beschibare Uren])
 
where:
 
Spoiler
 
Revenue Target =
CALCULATE(
SUMX('Resource Revenue Target','Resource Revenue Target'[Revenue Target]),
FILTER(
'Resource Revenue Target',
'Resource Revenue Target'[Year] = SELECTEDVALUE('Date'[Year Number])))

Spoiler
Contract Uren All =
CALCULATE(
[Contract Uren],
FILTER(
ALL( 'Date' ),
'Date'[Year Number] = YEAR(TODAY())))
Spoiler
Contract Uren =
CALCULATE (
SUMX (Resource, Resource[Contract Monday] ) * [Werkdagen],
'Date'[Day Number] = "1"
)
+ CALCULATE (
SUMX (Resource, Resource[Contract Tuesday] ) * [Werkdagen],
'Date'[Day Number] = "2"
)
+ CALCULATE (
SUMX (Resource, Resource[Contract Wednesday] ) * [Werkdagen],
'Date'[Day Number] = "3"
)
+ CALCULATE (
SUMX (Resource, Resource[Contract Thursday] ) * [Werkdagen],
'Date'[Day Number] = "4"
)
+ CALCULATE (
SUMX (Resource, Resource[Contract Friday] ) * [Werkdagen],
'Date'[Day Number] = "5"
)
Spoiler
Uren Vrij =
CALCULATE(
[Uren],
'Hour Transactions'[HourType] = 9
|| 'Hour Transactions'[HourType] = 10
)
Spoiler
Beschibare Uren =
[Contract Uren]
- CALCULATE ( [Uren], 'Hour Transactions'[HourType] = 9 || 'Hour Transactions'[HourType] = 10 )
For privacy reasons I can't publish the report.
 
Can anyone help me with this? 
 
Regards,
 
Hicham
 
Need Help

2 Replies

  • kranthi82's avatar
    kranthi82
    Icon for Helper I rankHelper I
    7 years ago

    Hello Anonymous ,

    Have you checked the relationships between the tables? 

     

    Try to make a dummy report if you can't publish yours so it's easier to help.

     

    /Kranthi 

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

    I don't know what the formula of Budget YTD means. You could try to use the function of TOTALYTD to calculate.

     

    Sales YTD = TOTALYTD(SUM('financials'[ Sales]), 'Table'[Date].[Date])

    Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

     

    Best Regards,

    Xue

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.