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.

Icey's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

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

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'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'[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'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.