DAX. Problem with subtotals and grand totals

Question

hope you are doing well and can help solve this puzzle in DAX for PowerBI and PowerPivot.

I'm having troubles with my measure in the subtotals and grand totals. My scene is the following:

I have 3 tables (I share a link below with a test file so you can see it and work there :robothappy:):

1) "Data" (where every register is a sold ticket from a bus company);

2) "Km" (where I have every possible track that the bus can do with their respective kilometer). Related to "Data";

3) and a "Calendar". Related to "Data".

In "Data" I have all the tickets sold from a period with their price, the track that the passenger bought and the departure time of that track.

Each track can have more than 1 departure time (we can call it a service) but only have a specific lenght in kilometers (their kilometers are specified in the "Km" table).

Basically what I need is to calculate the revenue per kilometer for each service in a period (year, month, day).

The calculation should be, basically:

Sum of [Price] (each ticket sold in the period) / Sum of [Km] (of the period considerating the services with their respective kilometers)

I managed to calculate it for the day granularity with the following logic and measures:

Revenue = SUM(Data[Price])

Unique dates = DISTINCTCOUNT(Data[Date])

Revenue/Km = DIVIDE([Revenue]; SUM(Km[Km])*[Unique dates]; 0)

I created [Unique dates] to calculate it because I tried to managed the subtotals of track granularity taking into account that you can have more than 1 day with services within the period. For example:

For "Track 1" we have registered:

1 service on monday (lunes) at 5:00am.

Revenue = $1.140. 
Km = 115. 
Tickets = 6. 
Revenue/Km = 1.140/115 = 9,91.

1 service on tuesday (martes) at 5:00am.

Revenue = $67. 
Km = 115. 
Tickets = 2. 
Revenue/Km = 67/115 = 0,58.

"Subtotal Track 1" should be:

Revenue = 1.140 + 67 = 1.207.
Km = 115 + 115 = 230.
Tickets = 6 + 2 = 8.
Revenue/Km = 1.207/230 = 5,25.

So at that instance someone can think my formula worked, but the problem you can see it when I have more than 1 service per day, for example for Track 3. And also this impact in the grand total of march (marzo).

I understand that the problem is to calculate the correct kilometers for each track in each period. If you check the column "Sum[Km]" is also wrong.

Here is a table (excel file to download - tab "Goal") with the values that should appear:

[goal] https://drive.google.com/file/d/1PMrc-IUnTz0354Ko6q3ZvkxEcnns1RFM/view?usp=sharing

[pbix sample file] https://drive.google.com/file/d/14NBM9a_Frib55fvL-2ybVMhxGXN5Vkf-/view?usp=sharing

Hope you can understand my problem. If you need more details please let me know.

Thank you very much in advance!!!

Andy.-

Links you provided don't allow to access the files. You might need to change sharing permissions. — RADO

RADO RADO · Accepted Answer · 2019-05-13T19:09:08

Delete "Sum of Km" - you should always write DAX measures instead.

Create a new measure for the km traveled:

Total Km =
SUMX (
    SUMMARIZE (
        Data,
        Data[Track],
        Data[Date],
        Data[Time],
        "Total_km", DISTINCT ( Data[Kilometers Column] )
    ),
    [Total_km]
)

Then, change [Revenue/Km] measure:

Revenue/Km = DIVIDE([Revenue], [Total Km])

Result:

The measure correctly calculates km on both subtotal and total levels. The way it works:

First, we use SUMMARIZE to group records by trips (where trip is a unique combination of track, date and time). Then, we add a column to the summary that contains km for each trip. Finally, we use SUMX to iterate the summary record by record, and sum up trip distances.

The solution should work, although I would recommend to give more thoughts to the data model design. You need to build a better star schema, or DAX will continue to be challenging. For example, I'd consider adding something like "Trip Id" to each record - it will be much easier to iterate over such ids instead of grouping records all the time. Also, more descriptive names can help make DAX clean (names like km[km] look a bit strange :)

DAX. Problem with subtotals and grand totals

1 Answers