Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
GKK1
Helper II
Helper II

Help needed - Wrong Total in Matrix - Diff of two columns.

Hi Power BI community team,

 

I am getting wrong totals in power BI matrix from the difference of two columns. Instead of giving difference between two column total values , the difference column is adding value and giving me the total. As per the image below, I need to get the
Total of Sales Diff = Total Sales Budget - Total of Sales.  
I will appreciate your help.

GKK1_0-1706738533067.png

 

6 REPLIES 6
v-jiewu-msft
Community Support
Community Support

Hi @GKK1 ,

If I understand correctly, the issue is that you got wrong totals in matrix from the difference of two columns. Please try the following methods and check if they can solve your problem:

1.Ensure that the aggregation settings for the matrix visual are set correctly.

 

2.Create a measure to calculate. Enter the following DAX formula.

Sales Diff Measure = SUM(Table[Sales Budget]) - SUM(Table[Sales])

3.Replace the difference column with the measure.

 

4.Check that there are any filters applied to the visual that might be affecting the values.

 

If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format to make a deep troubleshooting? It would be helpful to find out the solution.

 

Best Regards,

Wisdom Wu

Hi @v-jiewu-msft ,

Your answer was correct if there is any filter. So, I had a day's filter which was affecting the totals. Is there any ways to have that filter some how and get the correct values? 
I want to see data in Current week and I will have budgets for full week, however, sales would be there just till the current day in the week. So if I dont want to see the budget value in the Difference column, how can I acheive that? 
Thanks in advance for your support. 

Thanks for your reply @v-jiewu-msft . I am already using the measure for the difference. And I have matrix as rows instead of columns

 

GKK1_0-1706751819148.png

GKK1_1-1706751894669.png

I am sharing the sample PBI file as well.Wrong Column total - Difference of two columns in Matrix 

 

 

Access denied message.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Bassically what I need is as per this image. The column should show the difference for the week's value and if we are in the middle of the week then there will be no difference(Null value) . And at the end the total should show the difference between Total Sales Budget - Total Sales. Currently, I am getting Sum of the Difference column which is  -60. However, I want to get  

Sales Budget - Sales = Diff in Sales Budget and Sales
14400119602440

GKK1_0-1706753012983.png

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.