Forum Discussion
Dax multiple conditional sum
Hi guys,
I have a table which looks like this:
| Store | Phases | Estimated cost |
| J&J | A | 1 |
| J&J | B | |
| J&J | C | 2 |
| J&J | D | 3 |
| TimLee | A | 5 |
| TimLee | B | 3 |
| TimLee | C | |
| TimLee | D | 1 |
In all the cases, if estimatedCost of B is blank C will have some estimated cost or if B has some cost the C will be blank
I have slicer where user can select any store name
I want to create a MEASURE which can do a conditional sum based on the following conditions:
1: if Phase B's estimated cost is blank measure = 1 otherwise measure = 2
Any pointers will be greatly appreciated 🙂
Ta
Shei
- Yes, a MEASURE does not have the row context of the table. You can try two options:
Option A:
Use SUMX(Table, IF( insert your formula here)
Option B:
Use the SELECTEDVALUE function around any column you want to reference. This will only return a value if there is 1 value for the selected column, so requires a specific table configuration.
I prefer Option A in this case.
4 Replies
- Not sure what the problem is? What formula do you want to use for Measure =1 and measure=2?
What configuration do you want to have in the final table display?
A simple IF or SWITCH should do what you want, unless you have a complex table configuration, then you may need to start looking at ALL or other similar options.Thank mate
I am having trouble with referencing table's columns in a measure. In excel, I would have said,
if(and(Phase = "B",Estimate Value >0), 1, 2)
It is a simple tab and simple if and condition but I am struggling to define it as a measure.
Ta
Shei
- Yes, a MEASURE does not have the row context of the table. You can try two options:
Option A:
Use SUMX(Table, IF( insert your formula here)
Option B:
Use the SELECTEDVALUE function around any column you want to reference. This will only return a value if there is 1 value for the selected column, so requires a specific table configuration.
I prefer Option A in this case.
Not totally sure on what you are looking for but you can make a table visual with your Store column and this measure. It will show 1 if the B value for that store is blank (and 2 if not).
B Blank = IF(ISBLANK(CALCULATE(MIN(Table[Estimated Cost]), Table[Phases] = "B")), 1, 2)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
