Forum Discussion

emionline's avatar
emionline
Frequent Visitor
6 years ago
Solved

Average by category and type

Good afternoon I have a problem that I cannot solve I have a table that has different types and categories of product and I want to calculate an indicator by adding the differences that exist between them.

TABLE

ID

TYPE

CATEG

COST

1

A

X

1524

2

A

Y

1544

3

A

Z

1569

4

B

X

1599

5

B

Y

1634

6

B

Z

1674

7

A

X

1719

8

A

Y

1769

9

A

Z

1824

10

B

X

1884

 

CALCULATION

 

CATEG

AVERAGE TYPE A

AVERAGE TYPE B

DIF

X

1621,5

1741,5

120

Y

1656,5

1634

-22,5

Z

1696,5

1674

-22,5

 

MEASURE TOTAL SUM DIF 75

 

I want a measure that calculates the sum of the averages by category and type, which can then be shown by any other dimension Can somebody help me Thank you

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi emionline ,

    I'm so sorry for my late reply.

    I modified the previous formula, you could use the following formula:

    averageA =
CALCULATE (
    AVERAGE ( 'Average table'[Cost] ),
    FILTER (
        ALL ( 'Average table' ),
        'Average table'[Category] = MAX ( 'Average table'[Category] )
            && [Type] = "A"
    )
)
    averageB =
CALCULATE (
    AVERAGE ( 'Average table'[Cost] ),
    FILTER (
        ALL ( 'Average table' ),
        'Average table'[Category] = MAX ( 'Average table'[Category] )
            && [Type] = "B"
    )
)
    Diff =
'Average table'[averageB] - 'Average table'[averageA]
    sumDiff = 
VAR _diff = [averageB]- [averageA]
Var _a = SUMX(ADDCOLUMNS(VALUES('Average table'[Category]),"sumDiff",CALCULATE([Diff],ALLEXCEPT('Average table','Average table'[Category]))),[sumDiff])
return
IF(HASONEVALUE('Average table'[Category]),_diff, _a)

    My visualization looks like this:

    Did I answer your question ? Please mark my reply as solution. Thank you very much.

    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,

    Eyelyn Qin

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi emionline ,

    I'm so sorry for my late reply.

    I modified the previous formula, you could use the following formula:

    averageA =
CALCULATE (
    AVERAGE ( 'Average table'[Cost] ),
    FILTER (
        ALL ( 'Average table' ),
        'Average table'[Category] = MAX ( 'Average table'[Category] )
            && [Type] = "A"
    )
)
    averageB =
CALCULATE (
    AVERAGE ( 'Average table'[Cost] ),
    FILTER (
        ALL ( 'Average table' ),
        'Average table'[Category] = MAX ( 'Average table'[Category] )
            && [Type] = "B"
    )
)
    Diff =
'Average table'[averageB] - 'Average table'[averageA]
    sumDiff = 
VAR _diff = [averageB]- [averageA]
Var _a = SUMX(ADDCOLUMNS(VALUES('Average table'[Category]),"sumDiff",CALCULATE([Diff],ALLEXCEPT('Average table','Average table'[Category]))),[sumDiff])
return
IF(HASONEVALUE('Average table'[Category]),_diff, _a)

    My visualization looks like this:

    Did I answer your question ? Please mark my reply as solution. Thank you very much.

    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,

    Eyelyn Qin

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

    emionline , Try like

    Avg Type= calculate(average(Table[COST]),allexcept(Table[TYPE]))

    Avg Categ= calculate(average(Table[COST]),allexcept(Table[CATEG]))

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

    emionline 

    please try to create three measures

    average type a = AVERAGEX(FILTER('Table','Table'[TYPE]="A"),'Table'[COST])

average type b = AVERAGEX(FILTER('Table','Table'[TYPE]="B"),'Table'[COST])

dif = [average type a]-[average type b]

    • emionline's avatar
      emionline
      Frequent Visitor
      6 years ago

      Thank you very much for the answer, it only remains to solve the measure that adds up the total averages.
      The final result should give 75
      That is the measure that I cannot solve and that I need to analyze with any other dimension of the table

    • emionline's avatar
      emionline
      Frequent Visitor
      6 years ago

      Thank you very much for the answer, it only remains to solve the measure that adds up the total averages.
      The final result should give -75
      That is the measure that I cannot solve and that I need to analyze with any other dimension of the table

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi emionline ,

    According to my understanding ,you want to calculate the average based on category and type, and then display the minus value, right?

    You could use the following formula:

     

     

    averageA =
CALCULATE (
    AVERAGE ( 'Average table'[Cost] ),
    FILTER (
        ALL ( 'Average table' ),
        [Category] = SELECTEDVALUE ( 'Average table'[Category] )
            && [Type] = "A"
    )
)
    averageB =
CALCULATE (
    AVERAGE ( 'Average table'[Cost] ),
    FILTER (
        ALL ( 'Average table' ),
        [Category] = SELECTEDVALUE ( 'Average table'[Category] )
            && [Type] = "B"
    )
)
    Diff =
[averageB] - [averageA]

     

     

    My visualizations look like this:

    Is the result what you want? If not, please upload some data samples and expected output.
    Please do mask sensitive data before uploading.
     
    Best Regards,
    Eyelyn Qin