cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Average by category and type

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:

8.7.foll.PNG

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

View solution in original post

6 REPLIES 6
Highlighted
Super User IX
Super User IX

Re: Average by category and type

@emionline , Try like

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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User III
Super User III

Re: Average by category and type

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

1.PNG

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan
Highlighted
Microsoft
Microsoft

Re: Average by category and type

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:

3.PNG

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
Highlighted
Regular Visitor

Re: Average by category and type

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

Highlighted
Regular Visitor

Re: Average by category and type

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

Highlighted
Microsoft
Microsoft

Re: Average by category and type

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:

8.7.foll.PNG

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

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors