Forum Discussion
nattran
Helper I
Helper IDAX query to return multiple results
Hi, I'm currently trying to set up a Gross Margin report. Here is the layout it should look: Region Revenue Total Revenue Salaries Contract Cost Total Cost GM = Rev - Tot...
Hi nattran ,
So, you want to show 2450 like "2450(79%)" and show "8440" just as "8440". Right?
If so, try this:
Actuals = VAR Revenue = CALCULATE ( SUM ( Rev[PD_TOT] ), Report[ACCGROUP] = 1 ) * -1 VAR Expense = CALCULATE ( SUM ( Rev[PD_TOT] ), Report[ACCGROUP] = 2 ) VAR GM = Revenue - Expense VAR GM_Percent = ROUND ( GM / Revenue, 2 ) RETURN IF ( HASONEFILTER ( 'YourTableName'[groupname] ),----The second level column in your Matrix Rows field. GM, IF ( HASONEFILTER ( 'YourTableName'[Partner Name] ),----The first level column in your Matrix Rows field. GM & " ( " & GM_Percent * 100 & "% )", GM ) )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nattranHelper I
Helper IHi Icey
It now gives the data as per below:
Can we show the % per partner ? For exam Andrew Bilton should be Total 2,450 (79%) not at the grand total level?
Thanks!
Icey
Community Support
Community SupportHi nattran ,
So, you want to show 2450 like "2450(79%)" and show "8440" just as "8440". Right?
If so, try this:
Actuals =
VAR Revenue =
CALCULATE ( SUM ( Rev[PD_TOT] ), Report[ACCGROUP] = 1 ) * -1
VAR Expense =
CALCULATE ( SUM ( Rev[PD_TOT] ), Report[ACCGROUP] = 2 )
VAR GM = Revenue - Expense
VAR GM_Percent =
ROUND ( GM / Revenue, 2 )
RETURN
IF (
HASONEFILTER ( 'YourTableName'[groupname] ),----The second level column in your Matrix Rows field.
GM,
IF (
HASONEFILTER ( 'YourTableName'[Partner Name] ),----The first level column in your Matrix Rows field.
GM & " ( " & GM_Percent * 100 & "% )",
GM
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.