Same Expression Different Results

[Bartonaldo]

Hi All,

Looking for help with a DAX expression/Calculated table.

Context; I am looking to build a data model which tracks tonnage with a split on festival related delivery and non festival related delivery.

The identifier is our SIC code and I have built a working model (screenshot 1).

However, I have taken THE EXACT SAME:

• Data Model/ Input Data
• DAX Expression
• Calculated Fields

So why am I not getting the same outcome??This screenshot shows the correct totals and reconciliation along the bottom

you can see here whilst the category value matches, the totals are all out in both tables
 

The Expressions in both Power BI dashboards is the same.

DAX Expressions as follows

Festivals =

Calculate(

    SUM(QTY[QTY x Weight]),

    FILTER('Sales Accounts CSV',

    'Sales Accounts CSV'[SIC] = "850")

)

Festivals (not) =

Calculate(

    SUM(QTY[QTY x Weight]),

    FILTER('Sales Accounts CSV',

    'Sales Accounts CSV'[SIC] <> "850")

)

Calculated Field as Follows

QTY x Weight =

Calculate(

    (SUMX(QTY,QTY[QTY])

    *

    Sumx(QTY,QTY[Unit Weight (Added Column)]))/1000

)

Any help much appreciated!

Thanks in advance.

Regards,

Chris

[Bartonaldo · Accepted solution]

Thanks for the responses.

I'm not sure if it's my lack of understanding or experience but I couldn't implement the solutions in the above suggestions.

What I have done is use the "LOOKUPVALUE" function and created a calculated coumn in my data.

So rather than using a relationship between tables I've essentially used a VLOOKUP as you do in Excel to classify the row.  

I'm not sure it's the best/most efficient/proper way to do this but at least it's worked.

[_onto_]

Hi Chris,

Screenshot 1 shows Depot Name Revised, screenshot 2 shows Region. Are these two fields the same? Do they come from the same table?

QTY x Weight - is this a calculated column? The definition seems a bit convoluted. Since both fields come from the QTY table, why not do QTY[QTY] * QTY[Unit Weight (Added Column)] /1000 ? Or in a measure, [QTY x Weight] = SUMX(QTY, QTY[QTY] * QTY[Unit Weight (Added Column)] /1000)?

If you go the measure route the other measures would be:

Festivals = Calculate( [QTY x Weight], 'Sales Accounts CSV'[SIC] = "850" )

Festivals (not) = Calculate( [QTY x Weight], 'Sales Accounts CSV'[SIC] <> "850" ) 

Can you include a screenshot from the Manage Relationships window? Topology of your model is not clear.

[v-yueyunzh-msft]

Hi , @Bartonaldo 

According to your description, you use the same dax code to have different values in different places.
As for your problem, this is generally a problem caused by dax's filtering context.
First, you need to check if there is a "Filter" on your report or visual that results in a different value.
Second, for your dax code, you are using:
Festivals =
Calculate(
SUM(QTY[QTY x Weight]),
FILTER('Sales Accounts CSV',
'Sales Accounts CSV'[SIC] = "850")
)
Then when you place other fields, the 'Sales Accounts CSV' table will be affected by the current filtering context and return a different table, resulting in inconsistent calculation results.
You need to combine your own business logic to determine the correct results that different filtering contexts need to return. You can use functions such as ALL() to ignore some filter contexts to get the same value.

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Bartonaldo]

Aniya Zhang,

Thanks for your response, unfortunately I dont think this resovles my issue.

The filters on the page and on the visual are the same in both of my screenshot examples but you can see the Sum of the Total is not working on the second screenshot.

What I want to be able to show/prove is that Festival + Non-Festival = Total

The first screenshot this works fine.

The second screenshot, neither across or down totals are correct and I can't understand why?

I would struggle to upload sample data as this is potentially business sensitive.

I look forward to your response or any further advice.

Thanks in advance.

Regards,

Chris

[v-yueyunzh-msft]

Hi, @Bartonaldo 

Thanks for your quick response ! According to your description, you just confuse about the second screenshot for the total .
For your total in the vertical direction, it is eligible：

But the total is not the same, is my understanding correct?

For your question, you can see that the dimension of your first visual object is SIC, and the dimension of your second visual object is SIC+Reigion. So the vertical dimension for the second visual object is the same as the first visual object, so the calculated value is also the same.
The possible reason for the total inconsistency is that the filter context changes caused by placing different dimensions, which affects the inconsistency of the measure calculation results at the Total level.

For your first visual object, you can find that after you place the dimension, the total is not the sum of all the above values, but the calculation result according to the filtering context of Total:

For example, here you want to display the sum above, you can try to create a new measure based on this measure to refactor the filter context:

Festivals(not)-2 = SUMX(values('Table'[SIC]),[Festivals(not)])

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly