Forum Discussion

nattran's avatar
nattran
Icon for Helper I rankHelper I
6 years ago
Solved

DAX 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...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

nattran's avatar
nattran
Icon for Helper I rankHelper I
6 years ago

Hey Icey 

 

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

Andrew TesIncome ContributionFees2500
  WIP Movements600
 Total 3100 (100%)
 Direct ExpensesBad 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's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    nattran
    Icon for Helper I rankHelper I
    6 years ago

    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's avatar
      Icey
      Icon for Community Support rankCommunity Support
      6 years ago

      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.