How to divide rows with a measure from the same table

[JiriRak]

Hi,

I have got a table that combines the following financial and quantitative data:

I want to calculte the unit costs.

I have got the following measures:

2018 = CALCULATE ( SUM( Table[curr] ), Table[year]=2018 )

2018qty = CALCULATE( SUM( Table[qty] ), FILTER( Table, Table[account] = "SalesAccount" ), FILTER( Table[year] = 2018 ) )

Then, obviously, if you do:

2018unitcost = [2018] / [2018qty]

the result is like this due to the filter context:

How can I persuade it to take into account the sales quantity for the other accounts?

I´ve been experimenting with the filters and variables, but without success.

Thanks,

Jiri

[Mikelytics]

Hi @JiriRak 

Can you please try to change the qty measure like this

2018qty = 
CALCULATE( 
   SUM( Table[qty] ), 
   Table[account] = "SalesAccount",
   FILTER( Table[year] = 2018 ) 
)

I made the change here (took way the FILTER formula

let the other measure as they are.

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[JiriRak]

Thanks, but it has not worked. It has produced the same result. I want to divide all the account´s currency values by the sales account quantity.

[Mikelytics]

What confuses me a little bit is that your pictures look like excel. Can you maybe show it in Power BI? Do you use measures or calculated columns?

[JiriRak]

Yes, it´s excel for demonstration. I´m using measures, not calculated columns.

See below the screenshots:

I understand the my qty measure ("2018 LITRY MLEKA", the middle column) shows the value only on the row where the account is filtered, and then the third column works. But how to tell PBI to use the qty of the first account to divide with it the financial values of the other rows.

2018 KC NA LITR = [2018] * 1000 / [2018 LITRY MLEKA]

2018 LITRY MLEKA = CALCULATE( SUM( Data[Quantity] ), Accounts[Account] = 601200, FILTER( Data, Data[Year] = 2018 ) )

[Mikelytics]

Hi @JiriRak  

Since you also reference another account in your matrix you will need to also put in:

2018 LITRY MLEKA = 
CALCULATE( 
   SUM( Data[Quantity] ), 
   Accounts[Account] = 601200, 
   ALL(Table[Mleko]),
   FILTER( Data, Data[Year] = 2018 ) 
)

For table please put inthe name of the table where Mleko comes from in the visual

If this does not work then I would need a picture of the data model and see which columns are in the visual from which table

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[JiriRak · Accepted solution]

Reading the online documentation for the ALL function carefully again, I corrected my measures like this:

2018curr_per_unit_sold = 
CALCULATE( SUMX( Data, Data[Curr], Data[Year] = 2018 ) / 
CALCULATE( [2018qty_sold], ALL( Data[Account] ) )

Then it works.

Thanks for help.

[JiriRak]

To make it really simple, imagine there is only one table: Data with columns Data[Year], Data[Account], Data[Curr] and Data[Qty].

2018 = CALCULATE( SUM( Data[Curr] ), Data[Year] = 2018 )

2018qty_sold = CALCULATE( SUM( Data[Qty] ), Data[Account] = 601200, FILTER( Data, Data[Year] = 2018 ) )

2018curr_per_unit_sold = [2018] / [2018qty]

I´ve tried to experiment with the ALL function as per your suggestion, but I´m getting the same result.