Split total by group

[ysherriff]

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,204 and there are 5 campaigns associated with it. I would like to split the $5,812,204 over the five campaigns. I tried it with this measure but its not working. Any help is appreciated.

Avg $ Global or Potential =

 CALCULATE(

     AVERAGE( Campaigns[Global or Potential]),

        FILTER(

         ALL( Campaigns),

           Campaigns[BC #] = MAX(Campaigns[BC #])))

[v-yadongf-msft · Accepted solution]

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.

[amitchandak]

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

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 · Accepted solution]

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 · Accepted solution]

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