cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Sum based on criteria on one column and exclude date from another column

hi, am a newbie here and looking for support on a calculation...

 

Scenarios: i need to sum the dataset with the following:

There are many transaction types. all transaction types except for "IWO_WH_PRG" should be summed based on the Effective date drop down filter on the report.

For the "IWO_WH_PRG" transaction type, the sum should ignore the effective date

 

Data set

Transaction TypeContractEntityJob NumberSub-functionActivity CodeUnitQuantityMan HoursEffective DateRevision
IAFC_ERN_PRDCD701892360158FA101B100120HR 1416.9223-Dec-20100
IAFC_ERN_PRDCD701892360158FA101B100135HR 1976.2923-Dec-20100
IAFC_ERN_PRDCD701892360158FA101B100730HR 316.0623-Dec-20100
IAFC_ERN_PRDCD701892360158FA101B100830HR 15.0123-Dec-20100
IAFC_ERN_PRDCD701892360158FA101B100930HR 99.5823-Dec-20100
IAFC_ERN_PRDCD701892360158FA101B101005HR 9.0423-Dec-20100
IAFC_ERN_PRDCD701892360158FA101B101006HR 1414.6930-Dec-20100
IAFC_ERN_PRDCD701892360158FA101B101007HR 322.3430-Dec-20100
IAFC_ERN_PRDCD701892360158FA101B101305HR 030-Dec-20100
IAFC_ERN_PRDCD701892360158FA101B101306HR 738.3530-Dec-20100
IAFC_ERN_PRDCD701892360158FA101B101307HR 20030-Dec-20100
IWA_WH_PRGD701892360158FA101B100120HR 1416.9223-Dec-20100
IWA_WH_PRGD701892360158FA101B100135HR 1976.2923-Dec-20100
IWA_WH_PRGD701892360158FA101B100730HR 316.0623-Dec-20100
IWA_WH_PRGD701892360158FA101B100830HR 15.0123-Dec-20100
IWA_WH_PRGD701892360158FA101B100930HR 99.5823-Dec-20100
IWA_WH_PRGD701892360158FA101B101005HR 9.0423-Dec-20100
IWA_WH_PRGD701892360158FA101B101006HR 1414.6930-Dec-20100
IWA_WH_PRGD701892360158FA101B101007HR 322.3430-Dec-20100
IWA_WH_PRGD701892360158FA101B101305HR 030-Dec-20100
IWA_WH_PRGD701892360158FA101B101306HR 738.3530-Dec-20100
IWA_WH_PRGD701892360158FA101B101307HR 15030-Dec-200
IWO_WH_PRGD701892360158FA101B100120HR 1416.9201-Nov-190
IWO_WH_PRGD701892360158FA101B100135HR 1976.2901-Nov-190
IWO_WH_PRGD701892360158FA101B100730HR 316.0601-Nov-190
IWO_WH_PRGD701892360158FA101B100830HR 15.0101-Nov-190
IWO_WH_PRGD701892360158FA101B100930HR 99.5801-Nov-190
IWO_WH_PRGD701892360158FA101B101005HR 9.0401-Nov-190
IWO_WH_PRGD701892360158FA101B101006HR 1414.6901-Nov-190
IWO_WH_PRGD701892360158FA101B101007HR 322.3401-Nov-190
IWO_WH_PRGD701892360158FA101B101305HR 001-Nov-190
IWO_WH_PRGD701892360158FA101B101306HR 738.3501-Nov-190
IWO_WH_PRGD701892360158FA101B101307HR 001-Nov-190

 

Result should be like below:

 

 

Drop Down Filter on Report   
Effective DateIAFC_ERN_PRDCIWA_WH_PRGIWO_WH_PRG
30-Dec-202675.382625.386308.28

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Hi, @MosesDhas 

 

It’s my pleasure to answer for you.

According to your description, If you don’t want the effective date to filter a certain type, it’s not very recommended that displaying in one visual, there will be ambiguity.

You can try like this:

IWO_WH_PRG  =SUMX(FILTER(ALL(Table1),[Transaction Type]="IWO_WH_PRG"),[Man Hours])

v-janeyg-msft_0-1603951326508.png

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

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

View solution in original post

1 REPLY 1
Highlighted
Microsoft
Microsoft

Hi, @MosesDhas 

 

It’s my pleasure to answer for you.

According to your description, If you don’t want the effective date to filter a certain type, it’s not very recommended that displaying in one visual, there will be ambiguity.

You can try like this:

IWO_WH_PRG  =SUMX(FILTER(ALL(Table1),[Transaction Type]="IWO_WH_PRG"),[Man Hours])

v-janeyg-msft_0-1603951326508.png

If it doesn’t solve your problem, please feel free to ask me.

 

Best Regards

Janey Guo

 

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors