CODEWITHSUNDEEP
daxssas-tabular
AmilaMGunawardana
AmilaMGunawardana

Reputation: 1830

Create a Dax Measure to get the average

Hi I have a Fact table called FactUser and Dimension table called Target. Target Dimension contains the target values for each user. I need to get the average target value for each user using a measure in tabular. How can I do that? If target value is in the Fact table its no issue. Since the Target value is in a Dimension I'm bit confused to get the average over a user and over a day. Below show my tabular model

enter image description here

Upvotes: 0

Views: 216

Answers (2)

AmilaMGunawardana
AmilaMGunawardana

Reputation: 1830

I have added the target value inside Fact table and got the average as below. I want to get the average over username and date.

var Pivoted= ADDCOLUMNS(
        SUMMARIZE(FactUser,DateDimension[Date],User[username])
        ,"AVERAGE TARGET",CALCULATE(sum(FactUser[Target]))

    )

return averagex(Pivoted,[AVERAGE TARGET])

Upvotes: 0

Seymour
Seymour

Reputation: 3264

If you provided example and test cases with solution, it would be much easier. If you need to perform aggregation on a field, then why it is not in the Fact Table?

According to best practices, Power BI models should be designed as Star Schema and in Star Schema measures are calculated only on Fact Tables. So my suggestion in this case is not to solve the problem with DAX, but solve it by changing your Model.

However, you might try something like the following measure:

AVERAGEX('FactUser', RELATED('Targets'[Value])

Sources: https://dax.guide/related/ https://dax.guide/averagex/

Upvotes: 1

Related Questions