Forum Discussion
Fact Table have a same column for Revenue, Standard Cost
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.
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
2 Replies
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
