cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Patelnitya Frequent Visitor
Frequent Visitor

Incorrect Row and Column Totals

Hello, 

 

I am having some trouble with the following situation. 

 

I have two tables that i am pulling data from. One table is from SQL from my erp system and the other one is from an excel file. 

I have done a merge query of the excel file onto the sql table. Works well. 

 

The SQL table contains the fields called Item code and Qty of Goods on Hand. The excel file contains the field for cost of item.

 

The problem i am having is that Qty of Goods on Hand (StkItem[Qty_On_Hand]) has a default summarization property of Average. I added a DAX calculated column formula as:

 

Cost of Good on Hand = StkItem[Qty_On_Hand]*StkItem[NewColumn.Cost]

 

StkItem[NewColumn.Cost] has a default summarization property of SUM

 

I am getting the row totals correct for Cost of Goods on Hand but the column total is wrong. What is the solution to this problem?

 

Any assistance is appreciable 

 

Thanks,

Nitya

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Incorrect Row and Column Totals

Hi Nitya

 

You can use SUMX function to solve your problem. It is called the iterator function in PBI and iterates through each row of the table and perform the calculation accordingly. 

Create a measure like this 

Cost of Goods on Hand(measure):=
SUMX (
    StkItem,
    StkItem[Qty_On_Hand]*StkItem[NewColumn.Cost])

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
1 REPLY 1
Super User
Super User

Re: Incorrect Row and Column Totals

Hi Nitya

 

You can use SUMX function to solve your problem. It is called the iterator function in PBI and iterates through each row of the table and perform the calculation accordingly. 

Create a measure like this 

Cost of Goods on Hand(measure):=
SUMX (
    StkItem,
    StkItem[Qty_On_Hand]*StkItem[NewColumn.Cost])

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.