Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Average by category and type

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

emionline

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2020
02:45 PM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

Eyelyn9

Microsoft

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-04-2020
03:13 AM

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 6

Highlighted
##

**Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!**

**Dashboard of My Blogs** !! YouTube Channel !! Connect on Linkedin

amitchandak

Super User IX

Re: Average by category and type

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2020
07:39 PM

@emionline , Try like

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

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

Proud to be a Super User!

Highlighted
##

ryan_mayu

Super User III

Re: Average by category and type

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2020
09:46 PM

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

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!

Proud a to be a Datanaut!

Thanks and BR

Ryan

Proud a to be a Datanaut!

Thanks and BR

Ryan

Highlighted
##

Eyelyn9

Microsoft

Re: Average by category and type

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2020
10:37 PM

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.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

Eyelyn Qin

Highlighted
##

emionline

Regular Visitor

Re: Average by category and type

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-07-2020
06:31 AM

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
##

emionline

Regular Visitor

Re: Average by category and type

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-07-2020
06:36 AM

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

Eyelyn9

Microsoft

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-04-2020
03:13 AM

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

Top Solution Authors

User | Count |
---|---|

423 | |

305 | |

83 | |

56 | |

53 |

Top Kudoed Authors

User | Count |
---|---|

538 | |

496 | |

172 | |

138 | |

110 |