Should sales volumes/quantities be included in the values column of a fact table?

[Anonymous]

Hi folks, I'm new to Power BI and am looking for some guidance/help on two issues.

1. I have set up a fact table that contains currency values as well as quantity values like below.

 P&L Item Value

Quantity	20
Gross Profit	80
Revenue	200
Taxation	20
Quantity	5
Revenue	190

Is it correct to do this? 

2. As a result, I'm having trouble getting a "per unit" value for P&L items because my quantity P&L item is in the fact table.

I tried this DAX code to create a per unit measure i.e. GP / Unit or Revenue/Unit:

per unit value = 

var Quantity = all(CALCULATE(sum('Fact PA Actuals 2015 - 2018'[value]), 'Fact PA Actuals 2015 - 2018'[P&L_ITEM] = "Quantity" ))

Return(
 divide(sum('Fact PA Actuals 2015 - 2018'[value]), Quantity)
)

But it's not working...

Thanks for the help and please let me know if you need any clarification!

[v-yulgu-msft]

Hi @Anonymous,

You may need below measure.

per unit value = 

var Quantity = CALCULATE(sum('Fact PA Actuals 2015 - 2018'[value]), FILTER('Fact PA Actuals 2015 - 2018','Fact PA Actuals 2015 - 2018'[P&L Item] = "Quantity" ))

Return(
 divide(sum('Fact PA Actuals 2015 - 2018'[value]), Quantity)
)

For more advice, please show us the sample data in source dataset (table structure and detailed records) and your desired output with examples. How to Get Your Question Answered Quickly

Regards,

Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft, 

Thanks for your help so far!

There are two related tables being used:

1. Fact Table (Fact Key (pk), P&L Item (fk), Value)

2. P&L item (P&L Item (pk), Item Name)

pk = Primary key

fk = Foreign key

Allow me to clarify the question:

If the sum of all "Quantity" P&L items in the table is 10, I would expect the below output 

Per unit output:

P&L Item Value Per Unit Value

Gross Profit	100	10
Total Costs	50	5
Discounts	10	1
Transport	15	1.50

I'm trying to extract and sum the "Quantity" P&L item values to get a total i.e. :

P&L Item Value

Quantity	2
Quantity	5
Quantity	2
Quantity	1

 and then divide all the other P&L items by that "Quantity" variable to get a per unit value i.e.:

P&L Item Value Per Unit Value

Gross Profit	100	10 (=100 divided by 10)
Total Costs	50	5 (=50 divided by 10)
Discounts	10	1 (=10 divided by 10)
Transport	15	1.50 (=15 divided by 10)

 Thanks for your help so far, I hope this clarifies my question.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

Make sure there existing a one to many relationship from P&L Item table to Fact Table.

Please add below measures into table visual together with 'P&L item'[Item Name].

Remember to remove the "Quantity" P&L item from the visual via applying "Visual Level Filters" as shown in the screenshot.

Sum Value = SUM('Fact Table'[Value])

P&L Item Value Per Unit Value =
[Sum Value]
    / CALCULATE (
        SUM ( 'Fact Table'[Value] ),
        FILTER ( ALL ( 'P&L Item' ), 'P&L Item'[Item Name] = "Quantity" )
    )

Best regards,

Yuliana Gu