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 jameswill ,
According to your statement, I think you can try measure as below.
SGM % = VAR _Revenue = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[reporting_currency] = "Revenue" ) VAR _StandardCost = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[reporting_currency] = "Standard Cost" ) RETURN DIVIDE ( _Revenue - _StandardCost, _Revenue )MySample:
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi jameswill ,
According to your statement, I think you can try measure as below.
SGM % = VAR _Revenue = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[reporting_currency] = "Revenue" ) VAR _StandardCost = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[reporting_currency] = "Standard Cost" ) RETURN DIVIDE ( _Revenue - _StandardCost, _Revenue )MySample:
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.