Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi there, newbie questons
I have flat datatable (800 thousand rows)
I want the sum DISTINCT PLACE,WRKNR,WRKROW,MECHANIC WHERE SOURCE = WORKORDER
something like this:
SELECT DISTINCT PLACE,WRKNR,WRKROW,MECHANIC
CALCULATE SUM(TPOH * TPOPROS / 100)
WHERE SOURCE is WORKORDER
But how?
Hi @jvrnicfi
Please try
TPO hours =
SUMX (
FILTER ( 'Table', 'Table'[SOURCE] = "WORKORDER" ),
'Table'[TPOH] * 'Table'[TPOPROS] / 100
)
Thank you for your answer, solution is correct. Result is same that my own answer example.
However i misleading my queston by my own answer. Thats why i change the picture to clarify my queston.
Main problem is how to calculate with distinct columns (there is many lines that must be ignored)
Hi @jvrnicfi
Please try
TPO hours =
SUMX (
SUMMARIZE (
FILTER ( 'Table', 'Table'[SOURCE] = "WORKORDER" ),
'Table'[WRKNR],
'Table'[WRKROW],
"@TPOH", SUMX ( TOPN ( 1, 'Table', 'Table'[PARTSROW], ASC ), 'Table'[TPOH] ),
"@TPOPROS", SUMX ( TOPN ( 1, 'Table', 'Table'[PARTSROW], ASC ), 'Table'[TPOPROS] )
),
[@TPOH] * [@TPOPROS] / 100
)
Total = [TPOH] * [TPOPROS]
CALCULATE(SUM([Total]), FILTER('TableName', DISTINCT([Source]) = "WORKOREDER"))
Thank you for your answer, solution is correct. Result is same that my own answer example.
I do the same focus as I did for "tamerj1"
However i misleading my queston by my own answer. Thats why i change the picture to clarify my queston.
Main problem is how to calculate with distinct columns (there is many lines that must be ignored)
Try this once,
DistinctSum = CALCULATE( SUM('Table'[TPOH] * 'Table'[TPOPROS] / 100),
'Table'[SOURCE] = "WORKORDER",
ALLEXCEPT('Table', 'Table'[PLACE], 'Table'[WRKNR], 'Table'[WRKROW], 'Table'[MECHANIC]) )
Hi @jvrnicfi for sum two columns and product betwen them you just need measure like Sum Measure. In visual select wanted column. I hope this help
Sum Measure =
SUMX (
FILTER ( WORKORDER, WORKORDER[SOURCE] = "WORKORDER" ),
WORKORDER[TPOH] * WORKORDER[TPOPROS]
)
Proud to be a Super User!
Thank you for your answer, solution is correct. Result is same that my own answer example.
I do the same focus as I did for "tamerj1"
However i misleading my queston by my own answer. Thats why i change the picture to clarify my queston.
Main problem is how to calculate with distinct columns (there is many lines that must be ignored)
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |