Adding Average and a Constant Threshold Column

[abdul26]

I have created a marix with the following details 

Rows: Company name 

Columns:   Week numbers 

Values - Values 

I wish to add a constant value threshold column. But if a create a measure with a constant value of 30, it will repeat for each column. (i.e. come under each week).

Also, I want to add an average column as well 

I have added a sample data. 

Thank You! 

[Anonymous · Accepted solution]

Hi @abdul26 ,

Here I create a sample to have a test.

I suggest you to create a Threhshold table and create a relationship between it and your Fact table.

Relationship:

Measure:

Measure = 
IF(HASONEVALUE('Table'[Week numbers]),SUM('Table'[Values]),AVERAGE('Table'[Values]))

Create a matrix as below, we will turn off "Stepped layout" in Row headers.

We will show average in column subtotal by measure. So we need to rename it.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[abdul26]

Thank you for the detailed reply @Anonymous