Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
LisaB
Helper III
Helper III

Average on distinct values + filter

Hi,

 

I would like to calculate the average value based on distinct values. However, it seems like my filters on page level messes things up:

 

Filters: Status = Active, Item No: 100||200||300

 

Contract header
No (unique)Status
CON001Active
CON002Active
CON003Inactive
CON004Active

 

Contract invoicing lines

Contract NoItem NoLine Amount
CON00110010 000
CON0011012 000
CON0023003 000
CON0031004 000
CON0031011 000
CON00420050 000
CON00420120 000

 

Result should be: (10 000 + 3 000 + 50 000) / 3 = 21 000

Also, if I put an additional filter on item no 200 the average should change to 50 000.

 

Please let me know if something is unclear.

 

Thanks 

 

Lisa

7 REPLIES 7
v-yuta-msft
Community Support
Community Support

@LisaB ,

 

The dax above needs to achieve/compare previous row but there's no row number in your original table, so we need to add an index from 0 or 1 as row number.

 

For Line Amount you want to achieve is always the first row in each Contract No, so we should also rank the rows in each Contract No using index column.

 

Community Support Team _ Jimmy Tao

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

Hi @v-yuta-msft ,

 

thank you for the clarification.

 

I don't think this formula will work for this case since there might be contracts when there is an active contract that does not include any of the ARR items. It might also be an inactive contract that include an ARR item. So only taking the first item (rank =1) will give the incorrect result.

 

Lisa

 

 

Hi,

 

I have attached an example file showing my report.

 

I wish to show the average based on the page and report level filters. However, I would the average value to change if filtering by clicking on a bar in the chart.

 

Hope this clarifies.

 

Thank you.

Lisa

 

Average_example

 

v-yuta-msft
Community Support
Community Support

@LisaB ,

 

Why does the Item No 101 and 201 not included? Could you please clarify more logic about this?

 

Community Support Team _ Jimmy Tao

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

 

Hi @v-yuta-msft,

 

The value I want to calculate is the average ARR. Items 101 and 201 are not included in the defintion of the ARR. Hope this clarifies.

 

Thanks.

L

@LisaB ,

 

Firstly, build a relationship between two tables like below:

2.PNG

Secondly, add an index column in table 'Contract invoicing lines', then create a calculate column using DAX below:

Rank = RANKX(FILTER('Contract invoicing lines', 'Contract invoicing lines'[Contract No] = EARLIER('Contract invoicing lines'[Contract No])), 'Contract invoicing lines'[Index], , ASC, Dense)

3.PNG Finally, create a slicer based on status column and create a measure in 'Contract invoicing lines' table:

Result = CALCULATE(AVERAGE('Contract invoicing lines'[Line Amount]), FILTER('Contract invoicing lines', 'Contract invoicing lines'[Rank] = 1))

1.PNG 

 

Community Support Team _ Jimmy Tao

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

Hi @v-yuta-msft ,

 

Thanks. Could you, if possible, please explain the functionality of index and what the rank formula does?

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.