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 ,

 

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.