Forum Discussion
rolf1994
Helper II
Helper IICalculated total showing the wrong number in directquery
Hi, I am trying to calculate the following:
Measure
DistinctQuantity = CALCULATE(DISTINCTcount([Id]),[IsActive]="Active")
Measure
TotalPrice= DistinctQuantity*sum(Price)
My data looks like this:
Price DistinctQuantity TotalPrice
A 10 1 10
B 0 100 0
C 0 899 0
Total 10 1000 10000
I want mu table to show a total of "10" but instead it shows 10000. I want to add a column with the distinctquantity but I am working in DirectQuery so i cannot use the Calculate function to add a calculated column. How can I fix this?
In this scenario, you TotalPrice should be eavaluated on each row level, so you should use SUMX() function.
Total Price = SUMX(Table3,Table3[Price]*[Total Quantity])
Regards,
2 Replies
- Greg_Deckler
Community Champion
In general, consider that the total line is really the measure evaluated in the context of ALL. You can add a if HASONEFILTER to your measure to calculate it for rows with the alternative being your calculation for your total row. I may have those reversed. It's early.
v-sihou-msftMicrosoft Employee
In this scenario, you TotalPrice should be eavaluated on each row level, so you should use SUMX() function.
Total Price = SUMX(Table3,Table3[Price]*[Total Quantity])
Regards,
