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

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.

12 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
    My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
    Appreciate your Kudos.

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

      Hi,

       

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

       

      thanks

      Nattran

      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi nattran 

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

        Could you provide an example?

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

    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

  • 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!