Forum Discussion
PowerBI Wrong Total Sum calculation
Hello PowerBI Community,
I have recently started using PowerBI and came across an issue in a report.
I am trying to present in a table the amount of working hours per "OA ID" (with "Customer" (which I have blurred)).
The issue is that the the Total Count at the bottom of the column Sum of OPT by OA (hrs) is not correct. The line by line amount is correct, but the total is way off.
When I export the data to Excel, I do obtain the correct amount (which is also displayed on my company's dashboard).
When I try to show the Sum of Total OPT by OA (hrs) in a Card, it also shows a wrong value.
Is there a DAX I can use to "correct" the Sum of Total OPT by OA (hrs) ?
All the data comes already from a PowerBI semantic model (from my company) and not from measures.
I have seen on the forum posts about wrong Total, but when it dealt with MIN, MAX or days.
I have searched on the web for a few hours but I did not seem to be able to find the root cause of the issue.
Do you have an idea about what could be wrong or how I could fix it to show the correct sum amount ?
Thanks in advance for the support
Think this should do it
SUMX(
VALUES( VW_ACTUALS_OPERATI[OA_ID] ),
VW_ACTUALS_ACT[OPT hours]
)
10 Replies
- Deku
Super User
Need to see the data model and definitions of any measures you are using
Hi !
Below a snapshot of the data model.
All the data I use as columns come straight from a PowerBI Semantic Model online from my company's data.
Let me know if that helps- Deku
Looks like your issue is likely from the bidirectional relationships. You can add a measure with the following pattern, to turn off the bidirectional relationships
Calculate(
Sum( table[value] ),
Crossfilter( one[key], many[key], OneWay)
)
Hello,
I presume the bidirectional relationship you are talking about is the one below:I am not really familiar yet with relationships.
In order to simplify, I have removed Customer from the table for now.
I noticed that when I count the amount of DISTINCT "OA ID" from two different groups, the results are different.
When I count from the group VW_ACTUALS_ACTUALOPERATIONACTIVITIES, the result is around 39,000.
When I count from the group VW_ACTUALS_OPERATIONACTIVITIES, the result is 145 (this is the correct one).
I have tried the formula you shared earlier (Measure name = Test), however it still returns the wrong sum.
I tried inverting the arguments, but no success.
Let me know what you think- Deku
Ok you are summing from the many to the one. That means you are summing the same value multiple times, regardless for bidirectional or not.
What do you expect the calculation to do?
I would like the below Total
to be only the sum of OPT hours from the lines of the OA IDs in the table to the right (which belong to the data set VW_ACTUALS_OPERATIONACTIVITIES),
and not the sum of the OPT hours of all the OA IDs in the data set
VW_ACTUALS_OPERATIONACTIVITIES
I did some digging and I noticed that the OA ID Count in the table VW_ACTUALS_ACT... is around 39,000.
and the OA ID Count in VW_ACTUALS_OPERATI... is 145.
Is there a way to create a measure to calculate the Sum of OPT by OA (Hrs) but only for the OA IDs in the VW_ACTUALS_OPERATI... data set ?
Thanks in advance for the help and availability,
- Deku
Think this should do it
SUMX(
VALUES( VW_ACTUALS_OPERATI[OA_ID] ),
VW_ACTUALS_ACT[OPT hours]
)
Hi Totem ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hello !
Unfortunately the suggestions did not help me narrow down the issue.
I had to use an additional filter to remove the unnecessary columns.
Thanks for the support
Thomas
HersonDHelper I
Hi,
I have encountered a similar problem before.
You can make a workaround by creating a new column in your W_ACTUALS_ACT dataset.
Say label them as "1" and else is "0". This is just to group those ids that are in the VW_ACTUALS_OPERAT.
Example is using (if OA-ID in VW_ACTUALS_OPERAT = OA_ID in W_ACTUALS_ACT then 1 else 0)
You can then use that column in your Table Visual to filter those that has "1" value.
