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

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