Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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}}),
Upload the file to OneDrive or Google Drive and paste a link to it here.
Phil
Proud to be a Super User!
hope this can be accessible ..
I can't access that link, it requires a login.
Phil
Proud to be a Super User!
@PhilipTreacy arghhh yes it is my organizational account , so cannot access .. let me try to give private link ..
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
Proud to be a Super User!
Hi @PhilipTreacy Thanks for your response ..
I cannot able to attach the .pbix file as no options to attach it 😞
Hi! Some ideas:
I suppose you have done it, but just in case
Btw, you can attach a PBI file here
@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
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
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.
@mlsx4 Still it is not working 😞
Total Decimal values as 0.20 & 0.40 = 7.00 is missing to calculate as below :
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}
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 ..
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}}),
@mlsx4 Woooooaaahhhhhh its working now post changed the type as you mentioned above .
Thanks a lot for your support .. 🙂
I'm glad we could finally make it work 😊
User | Count |
---|---|
88 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |