Forum Discussion
Grand total calculation
Hi Anonymous ,
It is probably because the intermediate table is calculating a Blank or Empty value. For instance line 5 in your table.
Adjust the measure to exclude these result or adjust your filters to exclude these situations.
If you want to change the measure, you can add a filter statement to the Ratio New measure.
Since I don't have your data is a bit of guessing, but a blank value is most likely, otherwise adjust the filter accordingly.
Jan
Hi,
Modify your existing measure to
measure = IFERROR(SUM(Table1[Sales Amount])/SUM(Table2[Qty]),BLANK())
Now my measure will work.
Hi Sathish,
The reason for the total to be 280.6 is because PBI does not sum to row values in the Grand Total line, but it recalculates the formula. So the 280.6 is correct. You need a slightly more complex measure to sum your row values.
I used some sample data and I have rows per product category.
I created the following measures:
Sales Amount := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
Sales Qty := sum(Sales[Quantity])
Ratio := DIVIDE([Sales Amount], [Sales Qty])
Ratio New :=
Var CategoryRatio =
ADDCOLUMNS ( SUMMARIZE ( 'Sales', 'Product'[Category]), "Ratio", [Ratio] )
var Result = SumX ( CategoryRatio, [Ratio])
return
Result
Basically the intermediate table CategoryRatio calculates the row values, which are than summed to provide the grand total
Just translate it to your own data model / report
Hope this helps
Jan
if this is a solution for you, don't forget to mark it as such. (and kudos are always welcome too), thanks
Anonymous hi,
I had tried your Query but its showing the grand total as infinity.