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

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

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

 Quantity20

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @mcmanl01 ,

 

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" )
    )

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

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

Hi @mcmanl01,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
mcmanl01 Frequent Visitor
Frequent Visitor

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

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 Profit10010
Total Costs505
Discounts101
Transport151.50

 

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

P&L Item Value

Quantity2
Quantity5
Quantity2
Quantity1

 

 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 Profit10010 (=100 divided by 10)
Total Costs505 (=50 divided by 10)
Discounts101 (=10 divided by 10)
Transport151.50 (=15 divided by 10)

 

 

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

Community Support Team
Community Support Team

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

Hi @mcmanl01 ,

 

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" )
    )

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.