Total of a calculated measure is not coming correct

[Akansha2025]

Hi Team

I have a measure which is the calculated value of sales per 100 visits , The table is aggregated at region and year and month level 

DIVIDE(SUM(table1[sales]),MAX(table1[Visits]))

When I drill down to salesperson level i get the right answer but the total percentage at regionand year level is incorrect. 

Instead of summing up the individual sales /max visits per region and year month , it calculates sales/ max visits from all regions and year and month. 

How do i correct it , any help is appreciated. 

I want is 468/2497 * 100

[v-sgandrathi · Accepted solution]

Hi @Akansha2025,

Create Base Measures

Total Sales

[Total Sales] = SUM(table1[Sales])

Max Visits per Group (used later)

[Max Visits] = MAX(table1[Visits])

Next Create Matrix Visual

Rows: Region, Year, Month

Values:[Total Sales], [Visits], Custom % Measure

Create Percentage Measure

This measure calculates the correct percentage for each row, but the total will be incorrect:

[Sales per Max Visit] =
DIVIDE(SUM(table1[Sales]), MAX(table1[Visits])) * 100

At the total level, MAX does not preserve context, causing the total to be inaccurate.

Use SUMX Over a Grouped Table

Weighted Calculation (Total Sales / Total Max Visits)[Corrected Measure Weighted] =
VAR SummaryTable =
    SUMMARIZE(
        table1,
        table1[Region],
        table1[Year],
        table1[Month],
        "Sales", SUM(table1[Sales]),
        "MaxVisit", MAX(table1[Visits])
    )
RETURN
    DIVIDE(
        SUMX(SummaryTable, [Sales]),
        SUMX(SummaryTable, [MaxVisit])
    ) * 100

Thank you.

[v-sgandrathi]

Hi @Akansha2025,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

Thank you.

[v-sgandrathi]

Hi @Akansha2025,

Just checking in -- have you had a chance to review and try the provided solution? Kindly share the status whenever you get a chance.

Thank you.

[v-sgandrathi]

Hi @Akansha2025,

Thank you so much @MohamedFowzan1, @Hakuna_matata, @Ashish_Mathur, @rohit1991 for your responses regarding the issue.

We’re pleased to know that your query has been resolved. If the solution provided by the community member resolved your concern, please confirm.

Should you have any additional questions or require further assistance, feel free to reach out.

Thank you for your continued cooperation.

[MohamedFowzan1]

Hi @Akansha2025 
In place of ID use the SalesPerson

Based on the screenshot you have provided, it looks like you are asking to use sum of orders instead of sales

RowAndTotal =

IF(

    ISINSCOPE(Trial1[ID]),

    AVERAGEX(

        VALUES(Trial1[ID]),

        DIVIDE(SUM(Trial1[Sales]), MAX(Trial1[Visits]))

    ),

    DIVIDE(SUM(Trial1[Sales]), SUM(Trial1[Visits]))

)

Could you try if this works, please use Orders instead of Sales and give it a go as well

For OrdersL

RowAndTotalOrders =

IF(

    ISINSCOPE(Trial1[ID]),

    AVERAGEX(

        VALUES(Trial1[ID]),

        DIVIDE(SUM(Trial1[Orders]), MAX(Trial1[Visits]))

    ),

    DIVIDE(SUM(Trial1[Orders]), SUM(Trial1[Visits]))

)

The screenshot of using Sales and then Orders. Have given % for Sales as I dont believe thats what you need:

Please clarify if the understanding or expectation is not the same. Use *100 or change format of measure to % as necessary

Found this useful? 💡 Give a Kudo and mark as solution to guide others.

[Hakuna_matata]

Hi @Akansha2025 

I believe the DAX is calculating:

Total Sales / MAX(Visits across all rows)

I would suggest we improve our DAX by replacing it with 

  Sales per 100 Visits =
  VAR TotalSales = SUM(table1[Sales])
  VAR TotalVisits = SUM(table1[Visits])
  RETURN DIVIDE(TotalSales, TotalVisits) * 100

This will help us get the desired result as Sales = 468, Visits = 2497 → (468 / 2497) * 100 = 18.74% at the Region-Year-Month total level. 

If this helps please mark it as a solution. ^_^

[Akansha2025]

Hello , this is giving me an error ,in DAX.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the expected result there.

[rohit1991]

Hi @Akansha2025 

Could you please try below measure formula

% Sales per 100 Visits :=
IF(
    ISINSCOPE(table1[Salesperson]),
    DIVIDE(SUM(table1[Sales]), MAX(table1[Visits])) * 100,
    DIVIDE(SUM(table1[Sales]), SUM(table1[Visits])) * 100
)

[Akansha2025]

Hi Rohit

I tried this formula

Measure = IF(

    ISINSCOPE(tbl1[REGION]),

    DIVIDE(SUM(tbl1[ORDERS]), MAX(tbl1[Visits])) ,

    DIVIDE(SUM(tbl1[ORDERS]), SUM(tbl1[Visits]))

)

I am still not getting the correct percentage , Where you have salesperson , do we have to put region, month year in scope or just the salesperson?

I should be getting 18% but the value is 3 % when i use the above formula. What I need for my aggregate calcualteion is  is sum of all the max visists at salesperson level but with 3 % I think it it is doing 468/15600 ?