Final cost based on percentage of total

Anonymous — Thu, 15 Aug 2019 19:08:02 GMT

Hello,

I'm having trouble trying to calculate a final cost based on computer usage by an user per month.

Let's imagine the following scenario:

I have a lan-house with customers that use the computers for a certain amount of time, but my billing system doesn't give me the cost ($) for each user session, only by billing period.

I need to get the cost for each customer to pay, by billing period.

Tables scenario:

A table for billing. Each day of a machine within a billing period (e.g. day Jan 15th to Feb 15th, Feb 15th to Mar 15th) has a cost.

Each machine has a set of tags, in which I want to have only those tagged with "LAN"
I have multiple billing periods, which I want to filter to see one or more than one billing period

A table for sessions log. I have the session start date and session end date with the session duration.

What I have so far:

* A measure to sum all values with "LAN" tag

Invoiced USD = CALCULATE(SUM(Billing[Cost]),CONTAINSSTRING(Billing[Tag],"LAN"))

* A calculated table to get the sum of session duration by customer and billing period

Sessions_Consolid = SUMMARIZE(Sessions,'Sessions'[Customer],'Sessions'[Billing period],"customer.billingperiod",CONCATENATE('Sessions'[Customer],CONCATENATE(".",'Sessions'[Billing period])),"User acum. session duration",SUM('Sessions'[Session duration]))

* A relationship between Sessions_Consolid and Sessions table on column customer.billingperiod (filter on both directions)

* A measure to get the total session time

Total session duration = CALCULATE(SUM(Sessions_Consolid[User acum. session duration]),ALLSELECTED())

* A calculated column to get the weight of each customer

Weight = DIVIDE(Sessions_Consolid[User acum. session duration],CALCULATE([Total session duration],ALLSELECTED()),BLANK())

Could have created a measure here. I also tried, the same way I created the column.

* A measure to calculate the final value for each customer to pay

Final value = SUM(Sessions_Consolid[Weight]) * [Invoiced USD]

What I'm trying to do is:

I have some billing periods. When I filter on a slicer any billing period, or date, or whatever, I want it to give me the cost for that period and the weight of each customer for this select period.

On the images below I show that behavior. When no billing period is selected,the final value total is $ 154.60, with the Weight sum resulting 1.00.

When I select any value on the slicer, the total session duration changes but the weight formula is considering the "Total session duration" for all values, not the only showing on the table, and the final value considers this weight over total (not only the period I want) to calculate over the Invoiced USD column. As you can see, it results 34,61 (Final value) which is 61% (Sum of Weight) of 55,84 (Invoiced USD and the desired Final value).

This is the link to download the .pbix with dataset

https://1drv.ms/u/s!AlvWPQ8oZ0EVgtp4qbOvcxRL9hGWOQ?e=0NyCnN

Re: Final cost based on percentage of total

Anonymous — Thu, 15 Aug 2019 20:06:11 GMT

Solved changing columns by a measure with the same formulas after struggling for hours....

topic Re: Final cost based on percentage of total in DAX Commands and Tips

Final cost based on percentage of total

Re: Final cost based on percentage of total