cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trdoan Helper III
Helper III

Measure returns wrong result

Hello everyone,

 

Here is the link to my sample data.

 

Please note in the "Data" table, each Part Number corresponds to a COE Description and there are a total of 5 COE Descriptions.

 

Table "Data" and "OPR" are linked by Part Number and Material Number columns.

 

The "OPR" table is a transaction table where everything is recorded during the last 12 months whilst the "Data" table contains the Part Numbers I want to track to see whether Supplier WF and SOA had shared any Part Numbers mentioned in the "Data" table.

 

I did the below steps (not sure if they're right):

 

1. I merged the OPR and Data table, left outer join, by 'Data'[Part Number] and 'OPR'[Material Number] and called the table 'Merge Table'

 

2. I came up with this calculated column to find the Shared Material Numbers between WF and SOA, though I'm not sure the 'Merge Table'[Material Number] or the 'Merge Table'[Part Number] already did the job(?)

 

Please help me validate if merging the 2 tables already did the job of finding which Part Numbers they shared? Or the below measure would do/would not do the job?

 

Common P/Ns = 
VAR _MN = 
CONTAINS(
    FILTER('Merge Table', 
    'Merge Table'[Vendor Name] = "SOA"),
    'Merge Table'[Material number],'Merge Table'[Material number] ) 

&& 

CONTAINS(
    FILTER('Merge Table', 
    'Merge Table'[Vendor Name] = "WF"), 
    'Merge Table'[Material number], 'Merge Table'[Material number] )

RETURN IF(_MN = TRUE, 'Merge Table'[Material number] , "")

3. I created the following measures to calculate Spend, Volume, and Average Final Run Time (known as TAT):

SPEND = 
CALCULATE(
    SUM( 'Merge Table'[Net Order Quantity] ),
    CALCULATETABLE(
        VALUES( 'Merge Table'[Material number] ),
        'Merge Table'[Vendor Name] = "SOA"
    ),
        CALCULATETABLE(
        VALUES( 'Merge Table'[Material number] ),
        'Merge Table'[Vendor Name] = "WF"
    ))
VOLUME = 
CALCULATE(
    SUM( 'Merge Table'[Order Quantity] ),
    CALCULATETABLE(
        VALUES( 'Merge Table'[Material number] ),
        'Merge Table'[Vendor Name] = "SOA"
    ),
        CALCULATETABLE(
        VALUES( 'Merge Table'[Material number] ),
        'Merge Table'[Vendor Name] = "WF"
    ))
TAT = 
CALCULATE(
    AVERAGE( 'Merge Table'[Net Order Quantity] ),
    CALCULATETABLE(
        VALUES( 'Merge Table'[Material number] ),
        'Merge Table'[Vendor Name] = "SOA"
    ),
        CALCULATETABLE(
        VALUES( 'Merge Table'[Material number] ),
        'Merge Table'[Vendor Name] = "WF"
    ))

 

4. Because I wanted to have a dynamic chart where it can show Volume, Spend, and Average Final Run Time simultaneously, I created a new table called "Measure Selection" (see the below picture)

01.JPG

and the following measures:

Selected Metric = SELECTEDVALUE( 'Measure Selection'[Selected Measure], "SPEND" )
Selection = 
SWITCH(TRUE(),
    'Measure Selection'[Selected Metric] = "SPEND", [SPEND],
    'Measure Selection'[Selected Metric] = "TAT", [TAT],
    'Measure Selection'[Selected Metric] = "VOLUME", [VOLUME],
BLANK() )

5. I created a Clustered column chart and a chiclet slicer using 'Measure Selection'[Selection]

1.3.JPG

 

However, the result wasn't quite accurate. When I examined April 2018's calculations using a Data table, I noticed a couple of things:

 

1. In my original dataset, Supplier SOA's Volume in April 2018 only had 7 units whilst my [Volume] measure showed 14. 

 

In the below photo, I set "Order Quantity" as "Sum" and it gave 14 units and matched the [Volume] measure's result. Still, both of them should have showned 7.

Volume and sum.png

 

2. When I set "Order Quantity" as "Don't Summarise" and removed [Volume] from the data table, the result came out correctly.

dont summarise.png

 

3. But when I set "Order Quantity" as "Don't Summarise" and put back [Volume] to the data table, I got this:

1.4.png

 

All these calculations really wrecked my mind. Does anyone have any idea why my measure(s) did not calculate correctly? Please show me how to fix this. Thank you very much!

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: Measure returns wrong result

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

trdoan Helper III
Helper III

Re: Measure returns wrong result

Hi @Greg_Deckler , thanks for your response. I tried your measures but it didn't seem to work on mine.

 

The measure still returned 14 instead of 7.

 

Here  is the link to my sample pbi. Can you please take a look at it? Thanks a lot!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors