Wrong Grand total Issue

[Kumar Shanmugam]

I am doing on analysis on sale price. How many location have sale price below their state Average.
I am facing grand total issue. Grand total not matching with individual total or subtotal.
For eg in the attached sample file Haryana total is 7 and Maharashtra total is 8. So Grand total should be 15 instead of 16. So want to know why Grand total is showing an error.

To caculate state average, I have used following formula.
=Var StateAvgPrice=[StateAvgPrice]
Return
CALCULATE([NoofRepoSale],RepoSaleSample[AmountPaid]<StateAvgPrice)

 

[Ateeb Ali]

right click on total, click field setting, select custom then Sum, it will solve your problem

 

[Kumar Shanmugam]

It's not working. Please find attached the worksheet

 

[Ateeb Ali]

Please do with formula

 

[Kumar Shanmugam]

Thanks for the reply but this is not the result what i want. I want to calculate the number of cases where the sale price is below state average price.
This i want to calculate with a measure. I want my measure formula to calculate differently whilie evaluating grand total. In this case., my Measure should sum the individual value totals in the below state average field.

 

[Kumar Shanmugam]

Question Reframed.
I am doing on analysis on sale price. I want to find the count of sale price below their state Average in a location.
I have used the following measure to compute the same.
StateAvgPrice=CALCULATE([AvgPrice],ALLEXCEPT(RepoSaleSample,RepoSaleSample[State],RepoSaleSample[Product],RepoSaleSample[DisYear]))
BelowStateAvgPrice
=Var StateAvgPrice=[StateAvgPrice]
Return
CALCULATE([NoofRepoSale],RepoSaleSample[AmountPaid]<StateAvgPrice).
Above measure is working correctly for subtotal and individual total value but not for Grand total value.
For eg in the attached sample file Haryana total is 7 and Maharashtra total is 8. So Grand total should be 15 instead of 16. I want the computation of Grand total to be 15. What should be the modification in the measure formula of AboveStateAvgPrice. Please refer the sample file already attached.

 

[GraH - Guido]

Hi,

You have a measure StateAvgPrice with a grand total of 20917 (20916.5). Which is the average of the sub-totals per State 20208 + 21625.
The BelowStateAvgPrice is correct as is will count the LAN no where the AmountPaid is below 20916.5.
So nothing wrong with the formula. You need another measure that can handle that particular filter context. It has been a while since I wrote some DAX, so I can't give you a solution right away. Need to think about it a bit longer.

 

[Kumar Shanmugam]

Yes you are correct - that measure is working correclty but i want some modifiction so that it calcualtes differently whilie evaluating grand total. For eg, In this case it should sum up individual totals to come to 15 intead of 16.

 

[Chihiro]

Since your measure [StateAvgPrice] is calculated in context... total is calculated based on 20917. Thus giving 16 instead of 15 as answer.

There are several ways to avoid this issue. Almost all of them will involve creating dimension table in one form or another.

1. Create dimension table to assist in calculation.
You will need State dimension table. Add calculated column for Avg Amount. To be used as lookup table.

2. Doing the same in M Query. Easiest method here is to create copy of original table for manipulation purpose.
I'd recommend this approach over 1. As values calculated are static after initial load.

3. Using table variables to summarize table in stages. This is rather complex and can have performance overhead.
However, this method can give you dynamic result based on filter etc.

4. Add calculated column using DAX.
Again, issue here would be performance hit on report. Any interaction on the report will cause re-evaluation of calculated column and dependent measures.

If you are not familiar with dimension tables and common data model schema. Have a read of...

The importance of star schemas in Power BI - SQLBI

Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! This article shows why a st

www.sqlbi.com

For use of table/column variables in DAX, have a read of...
https://www.sqlbi.com/articles/table-and-column-references-using-dax-variables/

You will see why it's so important to start with appropriate model for analysis.

See attached for sample using method #2.

 

[GraH - Guido]

I knew Chihiro would deliver.

 

[Kumar Shanmugam]

Thank you Chihiro for the solution. 2nd Solution is easy to understand.
Is is possible to do something like this: if Hasonefiltter(Stockyard location), then CALCULATE([NoofRepoSale],RepoSaleSample[AmountPaid]<StateAvgPrice)
Otherwise do sum of Individual or subtotals.

 

[Chihiro]

The issue here is StateAvgPrice calculation context. So, no, HasOnevalue or filter won't cut it. You will need table variable to hold context evaluated values.

 

[Kumar Shanmugam]

Ok