Table totals are wrong because calculation also on total level

[rpinxt]

This is anoying:

Actually the only thing I want to total is the Saving.

But as you see the Saving is also totally wrong because it follows the calculation of the previous columns.

Is there a good way to handle this?

Also could switch it off and make a card with the Saving. But that number is also totally wrong...

[VijayP · Accepted solution]

@rpinxt 

In that case merge required records to Price IFU ( in powerquery) to get only relavant info and then you can result accordingly.

So this needs to be modeled properly!

[VijayP]

@rpinxt  

In this particular case it was data preparation issue not with any visual! To understand the route cause , it is always good to have details which I asked for. I am a trainer by profession hence this approach helped many!

www.youtube.com/perepavijay 

www.linkedin.com/in/vijayperepa

[VijayP]

@rpinxt 

"But I am still baffled that we need to go thru all this just to calculate cost if quantity and price are in different tables."

Answer to this question is , you need to have better datamodel not just few tables. Data Preparation makes bettter data model hence you have to go thru the same path

[VijayP]

@rpinxt 

Chagne the datatype to number for ink... and cost org columns.  then you will get the total

[rpinxt · Accepted solution]

Well Ok I now put also the calculation in the source data as a calculated column.

Now it works better with just the field or make a measure using DAX:

But I am still baffled that we need to go thru all this just to calculate cost if quantity and price are in different tables.

Just can't believe that this could not be done with just DAX between the 2 tables.

Also I still don't understand why it would not give me a total when only the price is a field.

So the field called 'Cost Org'.

What is different from that to field 'Sum of Cost Org2' or 'Test'??

[rpinxt]

Ok @VijayP I now merged the pricing the main table :

And at least the table looks better (I filtered out lines with no price):

But why it is not summing my Cost column?? Totals are on for the table.

IFU Qty = SUM('AVN IFU Printing'[Quantity])

Inkoopprijs Org is just the field that I merged in.

Cost Org = [IFU Qty] * SELECTEDVALUE('AVN IFU Printing'[Inkoopprijs Org])

[VijayP · Accepted solution]

@rpinxt 

In that case merge required records to Price IFU ( in powerquery) to get only relavant info and then you can result accordingly.

So this needs to be modeled properly!

[rpinxt]

So merging and processing everything (calculations) on a line level is the only way Power BI can handle this?

I thought comparing and calculating with two separate table was one of the strenghts of Power BI.

I will have a look if merging all the data together works better.

[VijayP]

@rpinxt  Please share your Kudos as well

[rpinxt]

Done. Although your "customer service" is not what I am used to here compared to other helpers.

[VijayP]

@rpinxt  Just curious whether my customer service was 👎 or  👍?

[rpinxt]

@VijayP the service was minimal to ok.

But I have made lots of posts on this forum and am used that people help with visual examples and not only words.

I added a sample sample and I am sure other helpers would have taken that sample and show the user how it needs to be done.

[VijayP]

@rpinxt  

This is because of bidirectional relation ship. If you change it to single , you will see the Totals matching with other values. What is the reason you selected bidirectional relation?

[rpinxt]

@VijayP you mean this bidirectional I guess?

Well if I set this to single I get :

That is even far worse. Because I should only have 3 lines with data at there are at the moment only 4 prices and 1 material is not used.

Now it give a price for every line (the same wrong price).

And actually the totals are still the same and wrong!

[VijayP]

@rpinxt 

If you can the scope interms business problem statement, then I can explain better. Modelling is one issue and Business problem statement is another!

What is the reason you wanted to see only few rows not all

[rpinxt]

Well @VijayP please look in the 'Pricing IFU' table in the example:

You see there only 4 IFU's that have Pricing.

If you look in the 'AVN Pricing IFU' table you will see lots of IFU's

Meaning that not all IFU's have pricing.

Only the ones that have pricing should show in the visual.

Or at least the ones that have no pricing should have a blank or 0 price.

Now for whatever reason PBI generates his own pricing for the lines that should have no price.

Also on every line 1,16 (Price Org) as a price is obvioulsy an error because there are at the moment 4 prices for Org(0.31, 0.30, 0.28 and 0,27).

The Business purpose is very simple.

You have a data table with IFU's and you have a pricing table with IFU's.

Find the corresponding price in the pricing table and muliply it with the quantity.

And if there is no price you calculate with 0 or you do nothing.

Very simple.

So best case the table only returns 3 rows, because in the data there are only 3 IFU's that have a price.

And logically the total of the table then should only be the total of these 3 lines.

[rpinxt]

Must be more strange and special than I thought.

I prepared a sample file.

Hopefully them somebody can find out what is going on.

You can find the sample file here :

https://drive.google.com/file/d/1LJJudjxgpqrn0n6y0BogRojf2eq301UJ/view?usp=sharing

Please let me know when you cannot access it.

[rpinxt]

Let me try to explain further with this screen :

My whole table has only 3 lines that are filled as you can see.

But look at the totals. Probably only the total for IFU Qty is correct. Even the 3 prices it doesn't even sum correctly.

Futher more the cost on the total line of 127K is 109K * 1,16 and the 19K on the total line for Cost SP is 109K * 0.1802.

And then it subtracts 129K with 19K and says total Saving is 107K.

But we can see that the saving is about 19K.

Why is the table not summing (totaling) the columns but using the same logic as measures for the fields in the table??

[VijayP]

@rpinxt  We need to see the measure (DAX) behind your calculation to see why the totals are wrong

[rpinxt]

Ok @VijayP 

Don't think they are that special.

Starting with the Quantity like :

IFU Qty = SUM('*** IFU Printing'[Quantity])

Then the Pricing:

IFU Price **** = SUM('Pricing IFU'[Inkoopprijs ****])

IFU Price SP = SUM('Pricing IFU'[Selfprint prijs PS])

From that I get the costs like :

Cost **** = [IFU Qty] * [IFU Price ****]

Cost SP = [IFU Qty] * [IFU Price SP]

And when I have the cost the 1 field I want to sum is the Saving like:

Saving = [Cost ****] - [Cost SP]

[rpinxt]

Maybe I should add to this that the list is longer. There is a filter on for the field IFU Price SP.

That cannot be blank.

So you see wrong totals because there are more lines in.

But of course I want only to see the totals of the lines visible.