Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
Can anybody assist with translating this Excel fomula to DAX.
=SUMIFS(S:S;K:K;K108;M:M;"INT_REPAIR";P:P;"Service")
The result I am looking for is a Column where the amount listed at ProductGroupID ' Service' is shown for the related CASEID and CASEID_TYPEID
Please note the CASEID_TYPEID has also different values in the table
Table in Power BI
Result I am looking for in Excel format
Solved! Go to Solution.
amitchandak - many thanks for the provided solution, it is providing the result I was looking for
Rgds
@BeeleJa ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi amitchandak, Is below sufficient?
For additional understanding - I want to either have the P_AMOUNT applied when for ProductGroupIS Service is is above zero, else I want the separate P_AMOUNTS to be applied and my intention is to use the result I am now lokking for in a further IF - THEN comparison
CASEID | CASEID_TYPEID | PRODUCTGROUPID | P_AMOUNT | SumIf result |
W00034470101 | INT_REPAIR | Service | 412.91 | 412.91 |
W00034470101 | INT_REPAIR | EPN | 412.91 | |
W00034470101 | INT_REPAIR | Parts APU | 0.15 | 412.91 |
W00034470101 | INT_REPAIR | Parts APU | 0.15 | 412.91 |
W00034470102 | INT_REPAIR | Parts APU | 4223.9175 | 50 |
W00034470102 | INT_REPAIR | Parts APU | 151.2225 | 50 |
W00034470102 | INT_REPAIR | Service | 50 | 50 |
W00034470102 | INT_REPAIR | Parts APU | 26.9775 | 50 |
W00034470102 | INT_REPAIR | Parts APU | 25853.85 | 50 |
W00034470102 | INT_REPAIR | Parts APU | 50 | |
W00034470103 | INT_REPAIR | Parts APU | 1694.385 | 352 |
W00034470103 | INT_REPAIR | Parts APU | 151.2225 | 352 |
W00034470103 | INT_REPAIR | Service | 352 | 352 |
W00034470103 | INT_REPAIR | Parts APU | 724.5975 | 352 |
W00034470103 | INT_REPAIR | Parts APU | 26.9775 | 352 |
W00034470103 | INT_REPAIR | Parts APU | 45917.025 | 352 |
W00034470103 | INT_REPAIR | Parts APU | 11.26 | 352 |
W00034470104 | INT_REPAIR | Service | 936 | 2456.49 |
W00034470104 | INT_REPAIR | Service | 1520.49 | 2456.49 |
W00034470105 | INT_REPAIR | Service | 651.64 | 651.64 |
W00034470106 | INT_REPAIR | Service | 0 | |
W00034470108 | INT_REPAIR | Service | 162.91 | 162.91 |
W00034470109 | INT_REPAIR | Parts APU | 109.1475 | 155 |
W00034470109 | INT_REPAIR | Service | 155 | 155 |
W00034470109 | INT_REPAIR | Parts APU | 3965.8575 | 155 |
W00034470109 | INT_REPAIR | Parts APU | 0.06 | 155 |
W00034470109 | INT_REPAIR | Parts APU | 0.32 | 155 |
@BeeleJa , create a new column
sumx(filter(Table, [CASEID] =earlier([CASEID]) && [PRODUCTGROUPID] = "Service"),[P_AMOUNT])+0
amitchandak - many thanks for the provided solution, it is providing the result I was looking for
Rgds
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |