Sum of a DIVIDE, then divided by DISTINCTCOUNT, then get the average across user ID column

[christinaxxx]

Hi, I'm trying to write a complex DAX to achive a visualisation on Truck Profitability but can't seem to work it out. 

For each Truck ID, I want to get the Gross Profit per Day. Say Truck A, it will be 15/5 = 3.
Then, I want to sum the above up to each User ID and divide that by the distinct count of Truck ID of that user to get the Truck Profitability for each user. So for User A, it will be (3+2)/2 = 2.5

The DAX should also be able to show national average (i.e., average of user A & B) when put in a table visual.

The hard part is that not all trucks go out every day within a period (e.g., a month so 30 days) so I have to aggregate the value bottom up by considering the number of days out for each.

User ID	Truck ID	Delivery Date	Gross Profit
A	Truck A	1/01/2024	1
A	Truck A	2/01/2024	2
A	Truck A	3/01/2024	3
A	Truck A	4/01/2024	4
A	Truck A	5/01/2024	5
A	Truck B	4/01/2024	1
A	Truck B	5/01/2024	2
A	Truck B	6/01/2024	3
B	Truck C	1/01/2024	1
B	Truck C	2/01/2024	2
B	Truck D	3/01/2024	3
B	Truck D	4/01/2024	4

[123abc]

To achieve the desired calculation of Truck Profitability for each user, you need to break down the problem into steps. You'll need to calculate the Gross Profit per Day for each truck, then sum it up for each user, and finally divide by the distinct count of Truck IDs for that user. Here's how you can do it in DAX:

1. Calculate Gross Profit per Day per Truck: You need to create a measure that calculates the Gross Profit per Day for each truck. This can be done by dividing the total Gross Profit for each truck by the count of distinct delivery dates for that truck.

Gross Profit per Day =
VAR TotalProfit = SUM('Table'[Gross Profit])
VAR DistinctDays = DISTINCTCOUNT('Table'[Delivery Date])
RETURN
DIVIDE(TotalProfit, DistinctDays, 0)

Calculate Truck Profitability for Each User: Now, you'll sum up the Gross Profit per Day for each user and then divide it by the distinct count of Truck IDs for that user.

Truck Profitability =
VAR TotalProfitPerUser =
SUMX(
VALUES('Table'[User ID]),
[Gross Profit per Day]
)
VAR DistinctTrucksPerUser = DISTINCTCOUNT('Table'[Truck ID])
RETURN
DIVIDE(TotalProfitPerUser, DistinctTrucksPerUser, 0)

1. Calculate National Average: To calculate the national average, you can directly use the Truck Profitability measure.

Now, you can use the Truck Profitability measure in your table visual, and it should give you the desired results. Make sure to adjust the table visuals and slicers as needed to display the data properly.

These measures assume that you have a table named 'Table' containing the columns User ID, Truck ID, Delivery Date, and Gross Profit. Adjust the column names accordingly if they are different in your dataset.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Expected result measure: =
VAR _grossprofit =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Truck[Truck ID] ),
        "@grossprofit", CALCULATE ( SUM ( Data[Gross Profit] ) ),
        "@daycount", CALCULATE ( COUNTROWS ( SUMMARIZE ( Data, 'Calendar'[Date] ) ) )
    )
VAR _dailyavg =
    ADDCOLUMNS (
        _grossprofit,
        "@dailyavg", DIVIDE ( [@grossprofit], [@daycount] ),
        "@truckcount", CALCULATE ( COUNTROWS ( SUMMARIZE ( Data, Truck[Truck ID] ) ) )
    )
VAR _result =
    AVERAGEX ( _dailyavg, DIVIDE ( [@dailyavg], [@truckcount] ) )
RETURN
    _result

[christinaxxx]

Thank you. This worked perfectly