• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate using other Table as Filter

Ricardo Mota

New Member
Hi All

I'm a Newbie in Powerpivot, so I'm struggling with this problem:

All the data are in one table called fTransactions.
In that table are the incomes and the costs, as lines: The Income are defined by
9110111001 to 9110111999 and the costs by several groups like: 91201 to 91208.

All this are in tables so you can see the income and the costs.

In the example I have a PT with the result that I obtain and another with the expected.

Im strugling with this for 2 days so... If anyone can help, much apreciated

Thank You

Ricardo Mota from Portugal
 

Attachments

  • Sample.xlsx
    248.5 KB · Views: 5
Hi Chihiro

I use CALCULATE( SaldoCC , table where are the data that I want to filter) to return only the values regarding the data there are in that table.

But doesn't work.

In the example I have the return and what I expected that PP return

Thank you very much
 
I don't have PowerPivot access at work so can't test now... but, your expression seems off.

Try
=CALCULATE(TableName1[Column],TableName2[Column]="xxxx")

Where "xxxx" is string for condition you want to filter for.

Or to sum column in Table1 with condition...
=CALCULATE(SUM(TableName1[Column]),TableName2[Column]="xxxx")
 
Hi Chihiro

my Formulas:

Custos Estrutura:=CALCULATE([SaldoCC];tbl_CustosEstrutura[Nr CCusto])
Outros Custos:=CALCULATE([SaldoCC];tbl_OutrosCustos[Nr CCusto])
SaldoCC:=[SomaCrédito]+[SomaDébito]
Saldo Marcas:=CALCULATE([SaldoCC];tbl_Marcas[Nr CCusto])
SomaDébito:=SUM([Débito])
SomaCrédito:=SUM([Crédito])
Total Custos:=[Custos Estrutura]+[Outros Custos]
Rateio:=[TotalQtd]/[Total Qtd (ALL)]
TotalQtd:=SUM([Qtd])
Total Qtd (ALL):=Calculate(fTransactions[TotalQtd];ALL(tbl_Marcas[Nr CCusto]))
Imputação Custos / Marca:=[Total Custos]*[Rateio]

Total Net:=[Saldo Marcas]-[Imputação Custos / Marca]

and the tables are in the screenshot attached

For the Formula:
Outros Custos:=CALCULATE([SaldoCC];tbl_OutrosCustos[Nr CCusto]) powerpivot will only return the values in SaldoCC that are being filtered by the tbl_OutrosCustos[Nr CCusto] ? Is my thinking correct?

Thanks
 

Attachments

  • 2016-03-16 15_18_20-Sample - Excel.png
    2016-03-16 15_18_20-Sample - Excel.png
    34.7 KB · Views: 7
o make it more simple:

If I have a fact table with Amount and costs and I want to distribute the costs by the Amount:

2016-03-16 16_52_23.png
Where TotalProdQty= 3+5+3 = 11
TotalCosts = -9€
Imputação = Total Costs * Rateio

The Costs are not in colums but in lines so what I thought is create a table with the name os the costs

tbl_Cost
Cost1
Cost2
Cost3

and then use
Costs:=Calculate(SUM(Amount);tbl_Costs) the result should be -9€

then

TotalProdQty = SUM(Qty) and
TotalProdQtyALL = Calculate(TotalProdQty;All(TotalProdQty) the syntax isn't correct but it's only to give the example

Rateio:= TotalProdQty/TotalProdQtyALL

Imputação: = TotalCosts*Rateio

tbl_Prod
Prod1
Prod2
Prod3

Net: = Calculate(Sum(Amount);tbl_Prod))

can you understand me like that ?

Thank you very much
 
Last edited:
Ok, I'm going into meeting but will take a look once I get home where I have PowerPivot loaded.
 
Ok your data model is not optimal.

What you need to do is first create 1 table with all unique Custos value. See attached.

However, you are trying to compute calculation that does not match line item (i.e. sum of all SaldosCC that is Estrutura or Outros, in rows that has Cust ID that's not relate to either of those values).

This will not sit well with DAX/Data model and will throw out error.

You will have to have additional pivot with filter to do your calculation.

Edit: In other words, PowerPivot and DAX isn't ideal for your need. Closest I can get with PowerPivot & DAX is below

upload_2016-3-16_23-38-26.png

You can see % Qtd of parent row total and show Grand Total for "Total Net". But not able to show line items for "Imputação Custos / Marca" or "Total Net".
 

Attachments

  • Sample (2).xlsx
    283.1 KB · Views: 3
Last edited:
Update Excel File ... with the correct answer... 3 days !

Thank you very much

Now trying to do the same in real data (Qty is in another table) and the relationship is not direct

EDIT: I posted this last night but got pending. So I will see your answer. Thank you very much for your help
 

Attachments

  • Sample.xlsx
    249.8 KB · Views: 4
Ah I see you removed Qtd from non-marcas items manually.

It's my opinion that manual manipulation of values (other than string clean-up etc), should never be done to retain data integrity.

Rather, if you know the number that is correct, then you should pick the right tool to arrive at the same number faster (with validation process).
 
In Original data Qty is in another table witch as only values for Marcas. So that's why I took the qty for non-marcas.

Marcas (Brands) are the sold itens, so I have it in another table with Qty.

In this Table I have the Revenue for and the COGS for Marcas as you can see in the sample.

Thank you
 
Ok your data model is not optimal.

What you need to do is first create 1 table with all unique Custos value. See attached.

However, you are trying to compute calculation that does not match line item (i.e. sum of all SaldosCC that is Estrutura or Outros, in rows that has Cust ID that's not relate to either of those values).

This will not sit well with DAX/Data model and will throw out error.

You will have to have additional pivot with filter to do your calculation.

Edit: In other words, PowerPivot and DAX isn't ideal for your need. Closest I can get with PowerPivot & DAX is below

View attachment 28610

You can see % Qtd of parent row total and show Grand Total for "Total Net". But not able to show line items for "Imputação Custos / Marca" or "Total Net".

I see what you have done in the Sample. Its easier to manage.

I was following that rule do not had to much calculated fields to Fact Table, but with your example I can do things much more easy

Thank you very much
 
Ok that makes sense. Mind, that I removed Qtd calculations and it's just a pivot field in my version (due to 1s in non-marcas line items). Glad you got it solved.
 
Sorry, another problem:

Now I have the Salesperson Costs that I must Join to every Marca(Brand)

The Table is this one:

2016-03-17 12_28_47-Sample.png

and I want to apply the costs of every salesperson to the respective brand that they sold: The Expected result :

2016-03-17 12_31_23-Sample.png
I had some costs to FTable: (Last 4 rows)

2016-03-17 12_31_44-Sample - fTable.png
 
cont...

I already tried with linked table:

and with disconnected table also

with Calculate and Filter for disconnected tables, but this time I can't establish a connection between the Brand and the salesperson
 

Attachments

  • SampleV2.xlsx
    290.2 KB · Views: 0
That's because data structure isn't right. You are mixing two sets of identifiers into single column. Never do that.

Instead, in the "tbl_Salesperson vs Marca" add a column for cost (salesperson) and link tables using Nr Custo Marca ID.
 
That's because data structure isn't right. You are mixing two sets of identifiers into single column. Never do that.

Instead, in the "tbl_Salesperson vs Marca" add a column for cost (salesperson) and link tables using Nr Custo Marca ID.


Ok. Will try that. Thank you once again
 
Hi Chihiro

Finally solved the problem:

Achieve to input the Salesperson Costs to the Brands!! Despite they are all in rows, with some filters and aux_calculations it's done.

Thank you very much for your help.

Ricardo
 
Back
Top