Fact Table have a same column for Revenue, Standard Cost

[sam_gift]

Hi, the fact table in my current model have a column called reporting_currency.

and there is an other column called rep_id

If the rep_id is 1,2,3,4 then reporting_currency is for Revenue.

If rep_id is 5,6,7,8 then reporting_currency is Standard Cost.

What is the better approach to maintain the fact table when I want to create different columns for Revenue and standard cost.

The reason, I am asking is I want to calculate SGM % which is DIVIDE((Revenue-Standard Cost)/Revenue) as a column to establish the Static Segmentation process for percentage bucketing.

[Anonymous · Accepted solution]

Hi @sam_gift ,

You can create a measure as below to get it:

GM % =
VAR _revenue =
    CALCULATE (
        SUM ( 'Table'[reporting_currency] ),
        FILTER ( 'Table', 'Table'[rep_id] IN { 1, 2, 3, 4 } )
    )
VAR _standardcost =
    CALCULATE (
        SUM ( 'Table'[reporting_currency] ),
        FILTER ( 'Table', 'Table'[rep_id] IN { 5, 6, 7, 8 } )
    )
RETURN
    DIVIDE ( _revenue - _standardcost, _revenue )

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Fowmy]

@sam_gift 

Please provide sample data and explain the expected results with examples.