Forum Discussion

shikharshah's avatar
shikharshah
Regular Visitor
3 years ago

Help understanding averagex with distinct count

I want to calculate the average items  per bill in a given dataset

From what I understand this formula:

 

Average lines per bill number = AVERAGEX(VALUES('20 Aug'[Bill No]),CALCULATE(DISTINCTCOUNT('20 Aug'[Item Name])))
 
It will create a table with the distinct bill no. then find the distinct item names per bill number and then it should find the average items per bill no.
 
This is not happening. It is returning total distinct items across the data set

From my research, I need to add a CALCULATE before the DISTINCTCOUNT formula. It is then correctly calculating average items per bill

Ask: Can someone help me understand WHY I need to put the CALCUALTE? From the documentation and tutorials I have seen, it should have calcualted the average items per bill without the calculate. Been trying to understand this for the past couple of hours. An explanation here will be really helpful!!
Need Help
Tutorial Requests

1 Reply

  • danextian's avatar
    danextian
    Super User
    3 years ago

    hI shikharshah ,

    The measure could have been simplified as without using CALCULATE but it wouldn't work as expected. Using an external measure within  a DAX expression  automatically wraps that measure within CALCULATE  so

    DistCount = DISTINCTCOUNT('table'[column]) is equivalent to CALCULATE([DistCount]). Not usingCALCULATE means the row context of VALUES('20 Aug'[Bill No]) is never converted to filter context so the same distinct count  is evaluvated for each unique item in Bill No.  Please see table below:

    There is a more detailed explanation in a similar post - https://community.fabric.microsoft.com/t5/Desktop/Using-measure-or-Aggregation-in-AVERAGEX/m-p/445133