Forum Discussion
Calculate Margin Based On Classification
Hi, I need to create a table to show Project's Margin (Cost/Revenue) based on matching Classifications using Table 1 & 2. One exception is Personnel and Personnel2 in Revenue should be added befo...
Hi Anonymous ,
As I mentioned in my reply above, based on your screenshot it's easy to see that you still haven't returned the variable you defined.
The error was generated because there was no RETURN. Here is the modified DAX statement:
Margin% = VAR PersonnelTotal = SUMMARIZE ( FILTER ( 'gbkmut_pns_drd', 'gbkmut_pns_drd'[artcode] IN { "PERSONNEL", "PERSONNEL2" } ), "Classification", "PersonnelTotal", "Revenue", SUM ( gbkmut_pns_drd[bdr_hfl] ) ) RETURN PersonnelTotalBest Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
I create two tables as you mentioned.
Then I create a new table and here is the DAX code.
Column = 'Revenue'[Revenue]Table =
VAR PersonnelTotal =
SUMMARIZE(
FILTER(
'Revenue',
'Revenue'[Classification] IN {"Personnel", "Personnel2"}
),
"Classification", "PersonnelTotal",
"Revenue", SUM('Revenue'[Revenue])
)
VAR OtherRows =
FILTER(
'Revenue',
NOT 'Revenue'[Classification] IN {"Personnel", "Personnel2"}
)
RETURN
UNION(
ADDCOLUMNS(
ROW("Classification", "PersonnelTotal", "Revenue", SUMX(PersonnelTotal, [Revenue])),
"Revenue1", SUMX(PersonnelTotal, [Revenue])
),
OtherRows
)
Finally I create a calculated column and get what you want.
Column =
VAR _Revenue1 = 'Table'[Revenue1]
VAR _Cost =
RELATED ( Cost[Cost] )
RETURN
IF (
'Table'[Classification] = RELATED ( Cost[Classification] ),
_Cost / _Revenue1,
BLANK ()
)
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.