Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 Type | Contract | Entity | Job Number | Sub-function | Activity Code | Unit | Quantity | Man Hours | Effective Date | Revision |
IAFC_ERN_PRDC | D7018 | 923 | 60158 | FA101 | B100120 | HR | 1416.92 | 23-Dec-20 | 100 | |
IAFC_ERN_PRDC | D7018 | 923 | 60158 | FA101 | B100135 | HR | 1976.29 | 23-Dec-20 | 100 | |
IAFC_ERN_PRDC | D7018 | 923 | 60158 | FA101 | B100730 | HR | 316.06 | 23-Dec-20 | 100 | |
IAFC_ERN_PRDC | D7018 | 923 | 60158 | FA101 | B100830 | HR | 15.01 | 23-Dec-20 | 100 | |
IAFC_ERN_PRDC | D7018 | 923 | 60158 | FA101 | B100930 | HR | 99.58 | 23-Dec-20 | 100 | |
IAFC_ERN_PRDC | D7018 | 923 | 60158 | FA101 | B101005 | HR | 9.04 | 23-Dec-20 | 100 | |
IAFC_ERN_PRDC | D7018 | 923 | 60158 | FA101 | B101006 | HR | 1414.69 | 30-Dec-20 | 100 | |
IAFC_ERN_PRDC | D7018 | 923 | 60158 | FA101 | B101007 | HR | 322.34 | 30-Dec-20 | 100 | |
IAFC_ERN_PRDC | D7018 | 923 | 60158 | FA101 | B101305 | HR | 0 | 30-Dec-20 | 100 | |
IAFC_ERN_PRDC | D7018 | 923 | 60158 | FA101 | B101306 | HR | 738.35 | 30-Dec-20 | 100 | |
IAFC_ERN_PRDC | D7018 | 923 | 60158 | FA101 | B101307 | HR | 200 | 30-Dec-20 | 100 | |
IWA_WH_PRG | D7018 | 923 | 60158 | FA101 | B100120 | HR | 1416.92 | 23-Dec-20 | 100 | |
IWA_WH_PRG | D7018 | 923 | 60158 | FA101 | B100135 | HR | 1976.29 | 23-Dec-20 | 100 | |
IWA_WH_PRG | D7018 | 923 | 60158 | FA101 | B100730 | HR | 316.06 | 23-Dec-20 | 100 | |
IWA_WH_PRG | D7018 | 923 | 60158 | FA101 | B100830 | HR | 15.01 | 23-Dec-20 | 100 | |
IWA_WH_PRG | D7018 | 923 | 60158 | FA101 | B100930 | HR | 99.58 | 23-Dec-20 | 100 | |
IWA_WH_PRG | D7018 | 923 | 60158 | FA101 | B101005 | HR | 9.04 | 23-Dec-20 | 100 | |
IWA_WH_PRG | D7018 | 923 | 60158 | FA101 | B101006 | HR | 1414.69 | 30-Dec-20 | 100 | |
IWA_WH_PRG | D7018 | 923 | 60158 | FA101 | B101007 | HR | 322.34 | 30-Dec-20 | 100 | |
IWA_WH_PRG | D7018 | 923 | 60158 | FA101 | B101305 | HR | 0 | 30-Dec-20 | 100 | |
IWA_WH_PRG | D7018 | 923 | 60158 | FA101 | B101306 | HR | 738.35 | 30-Dec-20 | 100 | |
IWA_WH_PRG | D7018 | 923 | 60158 | FA101 | B101307 | HR | 150 | 30-Dec-20 | 0 | |
IWO_WH_PRG | D7018 | 923 | 60158 | FA101 | B100120 | HR | 1416.92 | 01-Nov-19 | 0 | |
IWO_WH_PRG | D7018 | 923 | 60158 | FA101 | B100135 | HR | 1976.29 | 01-Nov-19 | 0 | |
IWO_WH_PRG | D7018 | 923 | 60158 | FA101 | B100730 | HR | 316.06 | 01-Nov-19 | 0 | |
IWO_WH_PRG | D7018 | 923 | 60158 | FA101 | B100830 | HR | 15.01 | 01-Nov-19 | 0 | |
IWO_WH_PRG | D7018 | 923 | 60158 | FA101 | B100930 | HR | 99.58 | 01-Nov-19 | 0 | |
IWO_WH_PRG | D7018 | 923 | 60158 | FA101 | B101005 | HR | 9.04 | 01-Nov-19 | 0 | |
IWO_WH_PRG | D7018 | 923 | 60158 | FA101 | B101006 | HR | 1414.69 | 01-Nov-19 | 0 | |
IWO_WH_PRG | D7018 | 923 | 60158 | FA101 | B101007 | HR | 322.34 | 01-Nov-19 | 0 | |
IWO_WH_PRG | D7018 | 923 | 60158 | FA101 | B101305 | HR | 0 | 01-Nov-19 | 0 | |
IWO_WH_PRG | D7018 | 923 | 60158 | FA101 | B101306 | HR | 738.35 | 01-Nov-19 | 0 | |
IWO_WH_PRG | D7018 | 923 | 60158 | FA101 | B101307 | HR | 0 | 01-Nov-19 | 0 |
Result should be like below:
Drop Down Filter on Report | |||
Effective Date | IAFC_ERN_PRDC | IWA_WH_PRG | IWO_WH_PRG |
30-Dec-20 | 2675.38 | 2625.38 | 6308.28 |
Solved! Go to Solution.
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])
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.
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])
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |