Decimal Values are not picked by SUMX measure

[dinesharivalaga]

Hi All ,

I am using this DAX to calculate volumes --> SUMX(FILTER('R2A GITM Data_New','R2A GITM Data_New'[Sub Activity Type]="Reconciliation FTE"),'R2A GITM Data_New'[Volume])

But in my source file contains both whole & decimal numbers in volumes column . but this above dax only accumulate whole numbers and skipped decimal numbers . expected result is 120.75 , but it showing only 112 (total whole numbers)

below is the missing decimal values .. i am not sure if it is happening due to Region & COuntry .
Because all the decimal values coming under regions which is having different country (kind of sub region)

please help to fix this asap ..

thanks in advance

dinesh

[PhilipTreacy]

@dinesharivalaga 

Upload the file to OneDrive or Google Drive and paste a link to it here.

Phil

[dinesharivalaga]

@PhilipTreacy 

https://capgemini-my.sharepoint.com/personal/dinesh-kumar_arivalagan_capgemini_com/Documents/R2A%20D...

hope this can be accessible ..

[PhilipTreacy]

Hi @dinesharivalaga 

I can't access that link, it requires a login.

Phil

[dinesharivalaga]

@PhilipTreacy arghhh yes it is my organizational account , so cannot access .. let me try to give private link ..

[PhilipTreacy]

Hi @dinesharivalaga 

Are you sure your FILTER isn't filtering out the decimal values?

It's hard to help you without seeing your data - please post it if possible.

Regards

Phil

[dinesharivalaga]

Hi @PhilipTreacy  Thanks for your response .. 

I cannot able to attach the .pbix file as no options to attach it 😞

[mlsx4]

Hi! Some ideas:

• Have you checked in Power Query that the type of the data is decimal number?
• Have you taken into account the , or . configuration as decimal separator?

I suppose you have done it, but just in case 

Btw, you can attach a PBI file here

[dinesharivalaga]

@mlsx4 thanks for your response ..

Yes i can see that the column volume is in correct data type (decimal)

how to check the decimal separator ? can you help how ?

thanks

Dinesh

[mlsx4]

You can check it in some value inside Power Query... I mean if the separator is in the correct position. Imagine you have a record which is 1.4 and when you check you find a 1400 value. In this case, Power BI will be taking a "." as thousand separator or vice versa

However, this might not seem to be the problem. Could you try to use CALCULATE? Just to check.

CALCULATE (SUM ('R2A GITM Data_New'[Volume]), 'R2A GITM Data_New'[Sub Activity Type]="Reconciliation FTE")

 
Btw, the link asks for an email. It is not a public link 

[dinesharivalaga]

@mlsx4  tried CALCULATE but still same .

[mlsx4]

And... have you tried to change the FILTER condition (just to dismiss options)? I mean instead of using "reconciliation FTE" 

CALCULATE (SUM ('R2A GITM Data_New'[Volume]), 'R2A GITM Data_New'[Sub Activity Type]="Reconciliation FTE"

you can try to do a filter by region, for instance:

CALCULATE (SUM ('R2A GITM Data_New'[Volume]), 'R2A GITM Data_New'[Regions]="Nordic")

 If this takes the values with the decimal points then our problem is in the other filter.

[dinesharivalaga]

@mlsx4 Still it is not working 😞

Total Decimal values as 0.20 & 0.40 = 7.00 is missing to calculate as below :

[mlsx4]

Therefore, it is a problem related to the numbers themselves, not the measure.

You should try to go to Power Query, delete the first change in the type of data and put them again as decimal values, because it is recognizing them as integers for sure (which adds 0 to your measure)

If you are familiar with the advanced editor, check it there. It should be {"Volume", type number} instead of {"Volume", Int64.Type}

[dinesharivalaga]

@mlsx4 

let
Source = Excel.Workbook(File.Contents("C:\Users\da9\OneDrive - Capgemini\Documents\Dinesh\CBS dashboards\R2A\Source FIles\R2A GITM Digital dashboard_Main File_Consolidated_Final.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Value (Func. Currency)", type number}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Value (Func. Currency)", null}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Errors",{{"Value (Group Currency €)", type number}}),
#"Replaced Errors1" = Table.ReplaceErrorValues(#"Changed Type1", {{"Value (Group Currency €)", null}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Errors1",{{"Volume", Int64.Type}, {"Month Year", type date}, {"Volume Percentage", Percentage.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type2", each true),
#"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows",{{"Volume", type number}}),
#"Replaced Errors3" = Table.ReplaceErrorValues(#"Changed Type3", {{"Volume", null}}),
#"Filtered Rows1" = Table.SelectRows(#"Replaced Errors3", each true),
#"Replaced Errors2" = Table.ReplaceErrorValues(#"Filtered Rows1", {{"Volume", null}}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Errors2", "Month Index", each if [Month] = "JAN" then 1 else if [Month] = "FEB" then 2 else if [Month] = "MAR" then 3 else if [Month] = "APR" then 4 else if [Month] = "MAY" then 5 else if [Month] = "JUN" then 6 else if [Month] = "JUL" then 7 else if [Month] = "AUG" then 8 else if [Month] = "SEP" then 9 else if [Month] = "OCT" then 10 else if [Month] = "NOV" then 11 else 12)
in
#"Added Conditional Column"

I think it is in correct data type only ..

[mlsx4 · Accepted solution]

You have a line, where first you change it to Integer: 
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Errors1",{{"Volume", Int64.Type}, {"Month Year", type date}, {"Volume Percentage", Percentage.Type}}),

Try to change it there:

#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Errors1",{{"Volume", type number}, {"Month Year", type date}, {"Volume Percentage", Percentage.Type}}),

[dinesharivalaga]

@mlsx4  Woooooaaahhhhhh its working now post changed the type as you mentioned above .

Thanks a lot for your support .. 🙂

[mlsx4]

I'm glad we could finally make it work 😊