Forum Discussion

ysherriff's avatar
ysherriff
Resolver II
4 years ago
Solved

Split total by group

In the image below, i have some duplicate values, which is okay but I would like to split the total over the entire group. For instance, in the below image, for BC#1598-01, it has a value of $5,812,2...
Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    Hi ysherriff ,

     

    Please try following DAX, you will get the SUM in total:

     

    Avg $ Global or Potential = 
IF(
    ISINSCOPE(Campaigns[Campaigns ID]),
    Divide(
CALCULATE(
AVERAGE( Campaigns[Global or Potential]),
FILTER(
ALL( Campaigns),
Campaigns[BC #] = MAX(Campaigns[BC #]))) ,
CALCULATE(
Count( Campaigns[Campaigns ID]),
FILTER(
ALL( Campaigns),
Campaigns[BC #] = MAX(Campaigns[BC #]))) ),
SUM(Campaigns[Global or Potential])
)

     

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • ysherriff's avatar
    ysherriff
    3 years ago

    Here is the formula:

    Thanks Yadong Fang. Below is the formula that was provided that was correct.

     

     

    Correct Average Total Amount =
    IF( ISINSCOPE( Campaigns[Campaign ID] ) ,
        [Average $ Global or Potential] ,
        SUMX(
            ADDCOLUMNS(
                SUMMARIZE(
                    Campaigns ,
                    Campaigns[Campaign ID] ,
                    Campaigns[BC #] ) ,
                "@Totals" ,
                [Average $ Global or Potential] ) ,
            [@Totals] ) )
amitchandak's avatar
amitchandak
Super User
4 years ago

ysherriff , Try

 

Avg $ Global or Potential =
Divide(
CALCULATE(
AVERAGE( Campaigns[Global or Potential]),
FILTER(
ALL( Campaigns),
Campaigns[BC #] = MAX(Campaigns[BC #]))) ,
CALCULATE(
Count( Campaigns[Campaigns ID]),
FILTER(
ALL( Campaigns),
Campaigns[BC #] = MAX(Campaigns[BC #]))) )

 

 

or

 

 

Avg $ Global or Potential =
Divide(
CALCULATE(
AVERAGE( Campaigns[Global or Potential]),
FILTER(
ALLSelected( Campaigns),
Campaigns[BC #] = MAX(Campaigns[BC #]))) ,
CALCULATE(
Count( Campaigns[Campaigns ID]),
FILTER(
ALLSelected( Campaigns),
Campaigns[BC #] = MAX(Campaigns[BC #]))) )

  • ysherriff's avatar
    ysherriff
    Resolver II
    4 years ago

    Thanks Amit but how do i show the total. The total is showing the averages but I want it to show the sum. It should be $99M

     

     

     

    • v-yadongf-msft's avatar
      v-yadongf-msft
      Community Support
      3 years ago

      Hi ysherriff ,

       

      Please try following DAX, you will get the SUM in total:

       

      Avg $ Global or Potential = 
IF(
    ISINSCOPE(Campaigns[Campaigns ID]),
    Divide(
CALCULATE(
AVERAGE( Campaigns[Global or Potential]),
FILTER(
ALL( Campaigns),
Campaigns[BC #] = MAX(Campaigns[BC #]))) ,
CALCULATE(
Count( Campaigns[Campaigns ID]),
FILTER(
ALL( Campaigns),
Campaigns[BC #] = MAX(Campaigns[BC #]))) ),
SUM(Campaigns[Global or Potential])
)

       

       

      Best regards,

      Yadong Fang

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • ysherriff's avatar
        ysherriff
        Resolver II
        3 years ago

        Here is the formula:

        Thanks Yadong Fang. Below is the formula that was provided that was correct.

         

         

        Correct Average Total Amount =
        IF( ISINSCOPE( Campaigns[Campaign ID] ) ,
            [Average $ Global or Potential] ,
            SUMX(
                ADDCOLUMNS(
                    SUMMARIZE(
                        Campaigns ,
                        Campaigns[Campaign ID] ,
                        Campaigns[BC #] ) ,
                    "@Totals" ,
                    [Average $ Global or Potential] ) ,
                [@Totals] ) )