SUM of displayed values

[Peter_2020]

Hi all, 

I have a table with data imported from SQL server with Direct query where I have 3 column adjusted by filter and column „Standard time“ is calculated by formula:

Standard time = IF(Vyroba[Operation]="0020",0.42) & IF(Vyroba[Operation]="0030",0.53) & IF(Vyroba[Operation]="0040",1.0) & IF(Vyroba[Operation]="0080",2.92) & IF(Vyroba[Operation]="0090",21.5) & IF(Vyroba[Operation]="0110",7.33)

It looks like this:

PO NUMBER	OPERATION	HOURS	STANDARD TIME
1060355894	0110	3,05	7,33
1060355895	0110	6,60	7,33
1060355896	0020	0,55	0,42
1060355896	0030	0,47	0,53
1060355896	0040	1,15	1,00
1060355896	0080	2,77	2,92
1060355896	0090	20,92	21,50
1060355897	0020	0,40	0,42
1060355897	0030	0,63	0,53
1060355897	0040	1,25	1,00
1060355897	0080	2,63	2,92
1060355897	0090	20,93	21,50
1060355897	0110	6,65	7,33
TOTAL		68,00

It shows me the numbers which SUM is 74,7. But when I define to create SUM for column „Standard time“ it takes me this:

PO NUMBER	OPERATION	HOURS	STANDARD TIME
1060355894	0110	3,05	14,66
1060355895	0110	6,60	36,65
1060355896	0020	0,55	0,42
1060355896	0030	0,47	1,06
1060355896	0040	1,15	2,00
1060355896	0080	2,77	5,84
1060355896	0090	20,92	258,00
1060355897	0020	0,40	0,42
1060355897	0030	0,63	1,06
1060355897	0040	1,25	2,00
1060355897	0080	2,63	8,76
1060355897	0090	20,93	365,50
1060355897	0110	6,65	36,65
TOTAL		68,00	733,02

It happens because I have defined that column „Hours“ gives me SUM of all numbers. When I turned off the SUM it gives me this:

PO NUMBER	OPERATION	HOURS	STANDARD TIME
1060355894	0110	1,22	7,33
1060355894	0110	1,2	7,33
1060355895	0110	0,15	7,33
1060355895	0110	1,35	7,33
1060355895	0110	1,52	7,33
1060355895	0110	1,62	7,33
1060355895	0110	1,97	7,33
1060355896	0020	0,55	0,42
1060355896	0030	0,07	0,53
1060355896	0030	0,40	0,53
1060355896	0040	0,57	1,00
1060355896	0040	0,58	1,00
...	...	...	...
TOTAL			733,02

Question is how to get SUM of numbers which are shown on the table below in column "Standard time" (result 74,7):

PO NUMBER	OPERATION	HOURS	STANDARD TIME
1060355894	0110	3,05	7,33
1060355895	0110	6,60	7,33
1060355896	0020	0,55	0,42
1060355896	0030	0,47	0,53
1060355896	0040	1,15	1,00
1060355896	0080	2,77	2,92
1060355896	0090	20,92	21,50
1060355897	0020	0,40	0,42
1060355897	0030	0,63	0,53
1060355897	0040	1,25	1,00
1060355897	0080	2,63	2,92
1060355897	0090	20,93	21,50
1060355897	0110	6,65	7,33
TOTAL		68,00

Thanks for help!

P

[v-zhenbw-msft · Accepted solution]

Hi @Peter_2020 ,

We can use two measures to meet your requirement.

1. Create a measure to assign a value to Operation.

Measure = 
SWITCH(
    TRUE(),
    MAX('Table'[OPERATION])="0110",7.33,
    MAX('Table'[OPERATION])="0020",0.42,
    MAX('Table'[OPERATION])="0030",0.53,
    MAX('Table'[OPERATION])="0040",1.1)

2. Then we can create a measure to get the result.

Standard time = SUMX(VALUES('Table'[PO NUMBER]),[Measure])

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Peter_2020]

Hello, 

thank you for your help! this is exactly what I was looking for.

just small adjustment and it works:

SUMX(SUMMARIZE(Table,[PO NUMBER], Table[Operation]), Table[STANDARD TIME])

Thank you very much!

[amitchandak]

@Peter_2020 , Try like

sumx(values(Table[PO Number]),Table[STANDARD TIME])

[Peter_2020]

Hi, you were not far. This works:

SUMX(SUMMARIZE(Table,[PO NUMBER], Table[Operation]), Table[STANDARD TIME])

[Greg_Deckler]

@Peter_2020 - Not sure I understand the issue. 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

[Peter_2020]

Dear Greg, thank you very much for very helpful information!