DAX query to return multiple results

[nattran]

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 - Total Cost

   GM%=GM/ Rev

My Dax calculation is as per below:

Actuals =
VAR Revenue = Calculate(sum(Rev[PD_TOT]),Report[ACCGROUP]=1)*-1
VAR Expense = Calculate(sum(Rev[PD_TOT]),Report[ACCGROUP]=2)
RETURN
Revenue - Expense

This gives me the right result for GM. But I'm trying to add the GM% underneath as well and can't figure out how.

Hope this makes sense.

Thanks

Nat

[nattran]

Hi,

Sorry, there is no option for me to upload a file here. Here is a screenshot of the sample file. I only have 1 measure that gives me the Gross Margin figure. But I'm hoping to add Gross Margin % underneath the Total as well. I wonder if we can return 2 sets of result in this measure Actuals?  I'm not sure if it is possible to achieve. If you have any other ideas please share. Many thanks

[Icey]

Hi @nattran ,

How about 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
    GM & " ( " & GM_Percent * 100 & "% )"

This measure will return something like below:

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nattran]

Thanks Icey. 

Is there a way for the % to only appear at the total level? So it will look like this:

Thanks and appreciate your help!

[Icey]

Hi @nattran ,

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'[Name] ),----The last level column in your Matrix Rows field.
        GM,
        GM & " ( " & GM_Percent * 100 & "% )"
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nattran]

Hey @Icey 

Thanks for your help. We're getting there. Here is what I got using your suggestion:

Andrew Tes	Income Contribution	Fees	2500
		WIP Movements	600
	Total		3100 (100%)
	Direct Expenses	Bad Debts Recovered	-100
		Salaries - Staff	-500
		Staff Recruitment Partners	-50
	Total		-650 (-inf%)
Total			2450 (79%)

I just wonder if there is a way Not to show the sub total % (3100 (100%)  & -650 (-inf%)). We only want to show the total % (2450 (79%))

Thanks and appreciate your help.

[Icey]

Hi @nattran ,

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'[PartnerName] ),----The first level column in your Matrix Rows field.
        GM,
        GM & " ( " & GM_Percent * 100 & "% )"
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nattran]

Hi @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 · Accepted solution]

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.

[Icey]

Hi @nattran ,

---

   Revenue

   Total Revenue

   Salaries

   Contract Cost

   Total Cost

   GM = Rev - Total Cost

   GM%=GM/ Rev

---

Are those records above all measures? And this visual is a Matrix, right? Please provide more details.

Best Regards,

Icey

[amitchandak]

@nattran , Try all as a separate measure

Revenue = Calculate(sum(Rev[PD_TOT]),Report[ACCGROUP]=1)*-1
Expense = Calculate(sum(Rev[PD_TOT]),Report[ACCGROUP]=2)
GM = Revenue - Expense
GM %= divide([Revenue] - [Expense],[Revenue])

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos.

[nattran]

Hi,

I actually want all the measures to show in 1 column. not in multiple columns.

thanks

Nattran

[az38]

Hi @nattran 

how do you imagine "all the measures to show in 1 column" ?

Could you provide an example?