Sum and average in a matrix - making blanks show as zero values and taken into consideration

[OSKEEER]

Hello, I have a problem i've been struggling with all day today.

I have a matrix showing categorys of products and articles  below each category. These I have as rows in the matrix. In the columns I show week - week 6 and 7 in my screenshot below.

Week by week and for each category and column I want to show the SUM of all sales made. In the total (bold marked in the screenshot below) I want the average to show.

It works but there is a problem that I need your help with, and that is that 0-values doesnt get calculated in the average.
So for example I want this to show:

Week 1 sales - 10 pieces
Week 2 sales - 0 pieces
Week 3 sales -  5 pieces
Average should be 5             (10+0+5)/3

In my database file of sales the product only shows in the current week if its been sold, if it hasnt been sold it doesnt show up - I have a bigger database for all the articles there is which is the foundation of the products showing in the matrix.

Below is the formula i use and a screenshot.

AverageSales =

COALESCE(

AVERAGEX(

    SUMMARIZE(

        'SalesTable',

        'SalesTable'[Category],

        'SalesTable'[Color],

        'SalesTable'[Article],

        'SalesTable'[Week],

        "Volym", SUM('SalesTable'[Volume])

    ),

    [Volym]

)

,0

)

Thanks in advance and kind regards

Oskar

[OSKEEER]

Hello, thank you for the response. Ill try here to explain better with samples.

I have two tables connected with a many to many relationsship - where SKU-code is the part connecting the two tables.

Article/settings table

Product name	Color	SKU-code	Category
Lamp	Green	1234	Lamps
Sofa	Blue	5623	Sofas
Standing lamp	Black	3357	Lamps
Small bed table	Brown	7500	Tables

Sales table

Volume	Week	SKU-code	Article
3	2	1234	Lamp
2	2	5623	Sofa
6	3	3357	Standing lamp
4	2	3357	Standing lamp

I want to create a Matrix in Power BI showing the following as rows, and weeks should be columns:

Category
Article
Color

All products should sum up per week, and the totals should be the average over the weeks shown/filtered.
This is the expected result im looking for

Category	Week 2	Week 3	Total
Lamps	3,5	3	3,25
+Lamp	3	0	1,5
+Standing Lamp	4	6	5
Sofas	2	0	11
+Sofa	2	0	1
Tables	0	0	0
+Small bed table	0	0	0

Hope this clarifys things and help 🙂 Kind regards

Oskar

[lbendlin · Accepted solution]

Your sample data does not warrant a many-to-many relationship.  Do you want to provide different sample data?

[lbendlin]

to report on things that are not there you need to use disconnected tables and cross joins.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...