SUM function doing wrongly calculation

[prasadpatil020]

Hi Community..!!
Find below screenshot for your information.

@@test is measure name, Day column is coming from calendar table and Tot service value gross coming from order level table, both the table connected using calendar_sk column.
I need to show sum of total service value gross in the column chart, but calculation showing wrong result. I am taking date to the x-axis and measure to y axis.

Kindly help me to resolve this.

[prasadpatil020 · Accepted solution]

 Hi All, Thank you so much for all your inputs and suggestions, my issue has been resolved by simple SUM fucntion, issue was in the data not in the DAX.
Once again thank you so much for your kind help..!!😊

[Jaywant-Thorat]

Hi @prasadpatil020 

If you want precise help, share two things

1. The exact DAX for @@test (so I can review it).

2. A screenshot of the Model view showing the relationship between your Calendar and Orders.

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach
Linkedin: https: https://www.linkedin.com/in/jaywantthorat/

#MissionPowerBIBharat = https://shorturl.at/5ViW9 

[prasadpatil020]

Hi @Jaywant-Thorat 
measure is just i am using

SUM('Order Level Details'[Tot. Service Value Gross])
Calendar table and order level table are connected via calendar_sk and relation is one-many single direction.

[rohit1991]

Hii @prasadpatil020 

The SUM function is not calculating wrongly  it’s working row by row because your measure is evaluated at the day level, and each row already contains the final value for that day. When you place Day on the axis, Power BI shows the measure per day, but when you put it in a column chart, the measure still returns the per-day value instead of the total. To get the full total across all days, you must create a measure like Total Service = SUM(‘OrderTable’[Tot Service Value Gross]) and use that in the visual without Day on the axis. If you keep Day on the axis, Power BI will always show the daily value, not the grand total.

[prasadpatil020]

Hi @rohit1991 ,
Thanks for the quick reply but that is my requirement , i need to keep date field on the x-axis.

[rohit1991]

Hii @prasadpatil020 

If you must keep the Date on the x-axis, then the SUM will always show the daily value, not the total. In that case you need a separate measure that ignores the day context, for example:

Total Service (Ignore Date) = CALCULATE(SUM('OrderTable'[Tot Service Value Gross]), ALL('Calendar')).

This removes the day filter so the column chart will show the full total even when Date is on the axis.

[prasadpatil020]

Hi @rohit1991 
After using your shared DAX.

[amitchandak]

@prasadpatil020 , Is this column [Tot. Service Value Gross] is used as "not summarised:? , because otherwise all Fridays should merge unless you have any other column in the visual 

What is used in visual, what you want to ignore 

I want to ignore all visual filter context  

Calculate(SUM('Order Level Details'[Tot. Service Value Gross]), allselected()) 

I want to ignore all filter context 

Calculate(SUM('Order Level Details'[Tot. Service Value Gross]), all()) 


Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[prasadpatil020]

Hi @amitchandak 
FYI, 

Just i need total of service value.
my Dax = 

CALCULATE(SUM('Order Level Details'[Tot. Service Value Gross]),ALLSELECTED('Calendar Dimension'))
I need filters should work so i have used ALLSELECTED.
If i use above dax then its showing total of all the service around one year, it should work on date filter from the slicer which is not happening. I want to use this calcualtion in column chart.
Calendar table and order level table are connected via calendar_sk and relation is one-many, single direction
Order numbers are duplicated hence resulting in wrong output, but if i pull Tot. Service Value Gross column directly in the table visual then its working fine but giving wrong result if i create measure.

[amitchandak]

@prasadpatil020 , 
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[prasadpatil020]

Hi @amitchandak 
I am not able to attache pbix file and sample csv data, its showing "The file type is not supported.
Hence adding a screenshot.

I need a measure where it will sum total value without ignoring duplicate order numbers as we can see the column total service value gross in the table visual.