Power BI table doesn't sum up

[Anonymous]

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

[v-xuding-msft]

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.

[kranthi82]

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