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. 

[Anonymous]

Hi @Akansha2025,

Thanks for reaching out to the Microsoft fabric community forum.

From what you’ve described, the root cause is the use of MAX(table1[Visits]) in your DAX formula. While this works at the lowest level (salesperson), when you go up to a higher level like region/year/month, it doesn’t behave the way you want. Instead of computing the percentage per group, it takes the overall maximum of the entire column, which leads to an incorrect total. As @FBergamaschi and @MohamedFowzan1 both responded to your query, kindly go through their responses and check if it solves your issue.

I would also take a moment to thank @FBergamaschi and @MohamedFowzan1, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[Anonymous]

Hi @Akansha2025,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround so that other users can benefit as well.  And if you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

[FBergamaschi]

This code

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

Does not calculate the average sale 100 per visit

So the first point is what you want to calculate

The average sal per visitis calculated in this way

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

Unless the Visits column is not what we believe and this is why I asked for a sample of data

Then we check the granularity thing once we get a clear example (after the definizione of the calculation as above indicated)

Othwerwise we are just speculating

[MohamedFowzan1 · Accepted solution]

Hi @Akansha2025 

In your scenario do you want to actually have sum(sales)/sum(visits) for the total case where as row wise, it should fetch the corresponding values. Then INSCOPE function would be useful. I tried to use this in Sample data and it works. Let me know if your usecase is not this.

RowAndTotal =

IF(

    ISINSCOPE(Trial1[ID]),

    AVERAGEX(

        VALUES(Trial1[ID]),

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

    ),

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

)

[FBergamaschi]

Hi @Akansha2025 

I am a bit confused on why you write MAX(table1[Visits]) at the denominator, but shall check the code once I have sample data from you

To solve, you will need to write a code that does the calculation with the granularity you have outlined otherwise Power BI will do the calculation in aggregation

In order for me to fix your code,

please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

[Akansha2025]