Now we faced the challenge as below, for the same dimension expression and same measure expression in straight table and pivot table, the measure value was not not matched. The straight table value was correct, Do you know any differnt between them? Also any advice how to check the issue here? Thanks in advance.
The expression was same, what you saw different was the variable expend in the pivot table.
Do you have any experience on this case ? Thanks in advance.
I'd try to use the exact same measure, thus without using the variable, and see if the issue persists. If it doesn't, the issue is the variable (the measure in it). If it does persist, we can look further into it.
Yes, we did that, but it also dont have the same value.
Now our dimension was like
And Measure was like
The field AA and AB are in the data model, AC not association with the data model, is it the root cause? Can you share some experience? Thanks
Could you possibly share an app with sample data? It would be a lot easier for us to help you if we can see what's happening and can change the measures instead of going back and forth with you on what might be the problem.
Thanks very much.
I attached the sample data, as you can see, when select A or B, the value was not same, the pivot table was not correct. Can you help to check? Thanks.
The problem is your table with the AC values. It's a different level of data (each value in AC represents two values in AA).
On top of that, using the dimension as it is defined, in a pivot table, it causes issues as it looks at your AC-values being matched to each AA-value.
Which is why in your sample, the values appear as if they are trippled in the pivot table (As AC has three values).
If you'd test a pivot table with the same formula, but with the dimensions AA or AB, the result would match your straight table.
Is there a reason why you need to use a pivot table and a straight table won't suffice?
A fix to show the right results in your pivot table would be a slight alteration to the measure.
I've uploaded your sample app again, but the measure edited, so you can test if it works as it should.