Total without average

[Anonymous]

Hi,

I would like some help on a tricky case :

In my dashboard, I had to create this measure :

Measure = (sum('Time'[Hours])/8*'People'[Rate])/COUNT('People'[Name])

The point is :

-In the first table (Time), I have names, dates, and hours. For one name, there are many rows, with different dates and hours.

-In the second table (People), I have names and rates. For one name, there is one row with the associated rate.

-The two tables are linked in Power BI on the names.

-The measure is calculating a fine by day, by people.

Problem is, when I put the measure in a table with dates, the total by date is wrong.

I know it comes from the different rates : if I keep two people with the same rate, the total is ok, and if I keep two people of different rates, then the total is wrong (I mean different than a total of the two rows), because the calculation is made with an average of the two rates.

I've searched a solution for this, such as this one : https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376#

Problem is : nothing I tried worked : Summarize, Hasonefilter, Calculate...

Even if I can get the measure working with calculate or hasonefilter, the total keeps using an average for the rates.

Can anyone help on this case ?

Best regards,

Martin.

[Ashish_Mathur]

Hi,

Share a small dataset and sho the expected result.  paste the tables in a format that can be taken to an MS Excel file. 

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

Realtionship:

Measure:

Measure = 
DIVIDE(
    DIVIDE(
        SUM(Table1[Hours]),
        8
    )*
    SUM(Table2[Rate]),
    COUNT(
        Table2[Name]
    )
)

You may create a new measure as below.

Avg Measure = 
AVERAGEX(
    SUMMARIZE(
        Table1,
        Table1[Name],
        Table1[Date],
        "Result",
        DIVIDE(
            DIVIDE(
                SUM(Table1[Hours]),
                8
            )*
            SUM(Table2[Rate]),
            COUNT(
                Table2[Name]
            )
        )
    ),
    [Result]
)

Result:

Best Regards

Allan

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

[Anonymous]

Hi Alan,

Thank you for your help.

Unfortunately, the solution doesn't work : if I build a table with Name and Measure, I get 0,63 for a, 0,20 for b, and 1,50 for c. The total should be 2,33, but we get 2,05.

Is there a way to calculate the measure to get 2,33 as total ?

Best regards,

Martin.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

You may modify the measure as below. The pbix file is attached in the end.

Sum Measure = 
SUMX(
    SUMMARIZE(
        Table1,
        Table1[Name],
        Table1[Date],
        "Result",
        DIVIDE(
            DIVIDE(
                SUM(Table1[Hours]),
                8
            )*
            SUM(Table2[Rate]),
            COUNT(
                Table2[Name]
            )
        )
    ),
    [Result]
)

Result:

Best Regards

Allan

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

[Anonymous]

Hi Alan,

It's perfect ! Thanks a lot !

Best regards,

Martin.