Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have the following set of data:
Company | PO Date | Item# | Unit Price | IsNET | Supplier |
001 | 1/6/2021 | 110 | 1.23 | TRUE | ABC |
002 | 3/4/2021 | 111 | 1.45 | FALSE | BCD |
003 | 5/8/2021 | 112 | 1.56 | TRUE | CBE |
001 | 8/8/2021 | 111 | 1.3 | FALSE | DEF |
003 | 9/6/2021 | 112 | 1.89 | FALSE | CBE |
I need to create a matrix or table showing the Unit Price (which divided into CPG and NET) for the latest PO date in the following format:
Item# | Supplier | |
CPG Price | NET Price | |
CPG Price condition: IsNET = FALSE + Company <> 0001
NET Price condition: (IsNET = TRUE + Company <> 0001) + Company = 0001
Much appreciated for any help!!
Solved! Go to Solution.
Hi @Helpful_Fun4848 ,
CPG =
var _latest = CALCULATE(MAX('Table'[PO Date]),FILTER('Table','Table'[IsNET]="FALSE"&&'Table'[Company]<>"0001"))
return
CALCULATE(SUM('Table'[Unit Price]),FILTER('Table','Table'[PO Date]=_latest))
NET =
var _latest = CALCULATE(MAX('Table'[PO Date]),FILTER('Table',('Table'[IsNET]="TRUE"&&'Table'[Company]<>"0001")||'Table'[Company]="0001"))
return
CALCULATE(SUM('Table'[Unit Price]),FILTER('Table','Table'[PO Date]=_latest))
Best Regards,
Jay
Hi @Helpful_Fun4848 ,
CPG =
var _latest = CALCULATE(MAX('Table'[PO Date]),FILTER('Table','Table'[IsNET]="FALSE"&&'Table'[Company]<>"0001"))
return
CALCULATE(SUM('Table'[Unit Price]),FILTER('Table','Table'[PO Date]=_latest))
NET =
var _latest = CALCULATE(MAX('Table'[PO Date]),FILTER('Table',('Table'[IsNET]="TRUE"&&'Table'[Company]<>"0001")||'Table'[Company]="0001"))
return
CALCULATE(SUM('Table'[Unit Price]),FILTER('Table','Table'[PO Date]=_latest))
Best Regards,
Jay
"NET Price condition: (IsNET = TRUE + Company <> 0001) + Company = 0001"
This will always evaluate to false. Please check the definition.
The statement on my OP may not be correct. The 2 conditions for NET Price:
1. IsNET = TRUE
2. Everything from Company 0001 including if IsNET = FALSE
CPG = 'Table'[IsNET]=FALSE() && 'Table'[Company]<>"001"
NET = 'Table'[IsNET]=TRUE() || 'Table'[Company] = "001"
These are calculated columns - no need for measures. Note that they are complementary, so you could also say
NET = NOT CPG
I need to grab the value of the Unit Price for the latest PO Date only. I don't think the calculated column on your post above did just that, right?
Much appreciated for trying to help!!
Based on your sample data, what would be the expected outcome? I'm still not clear on your process logic.
So, this is my data:
Company | PO Date | Item# | Unit Price | IsNET | Supplier |
0001 | 1/6/2021 | 110 | $1.23 | TRUE | ABC |
0002 | 3/4/2021 | 111 | $1.45 | FALSE | BCD |
0003 | 5/8/2021 | 112 | $1.56 | TRUE | CBE |
0001 | 8/8/2021 | 111 | $1.30 | FALSE | DEF |
0003 | 9/6/2021 | 112 | $1.89 | FALSE | CBE |
0003 | 10/1/2021 | 110 | $1.30 | TRUE | ABC |
And this is the outcome:
Supplier Name | CBE | ABC | ||
Item# | CPG | NET | CPG | NET |
112 | $1.89 | $1.56 | ||
110 | $1.30 |
Price on the outcome table is Unit Price from the latest PO Date for the respective supplier.
If I can summarize all the conditions:
1. Both CPG and NET = Unit Price from the latest PO Date
2. CPG: IsNET = FALSE
3. NET: IsNET = TRUE
4. Company 0001 = NET (even if IsNET = FALSE)
The table outcome is not correct after I pasting it here.
It should be:
CBE ABC
CPG NET CPG NET
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |