Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello! I have a problem that seems like it should be easy, but I can't seem to get the formula right.
I have two tables. One has values by asset by date, the second defines the type of asset. I need to create a measure or calculated column which sums the value by date and by type to use the values for additional analysis. The tables are related by Name. I am not sure how to filter the sum of the values by the Type. I do not want to see this in a table. I need to create a measure that sums by the filter so that I can use the value in other calculations. The result table below is only to show you an example of what the measure will need produce if I decided to put it into a table. I want to SUM the VALUE in Table 1 filtered by the TYPE in Table 2. So for example, I want the sum of cash for 1/31, 2/29, and 3/31. I have been trying to use the following formula, it does not error out in Dax, but it also does not product a result.
Calculate(SUM('Table1'[VALUE]), filter(ALL(Table2), (Table2[TYPE] = "Cash"&& 'Table1'[NAME DETAIL] = 'Table2'[NAME])))
Thank you very much for your help.
Example:
Table 1 | ||
Date | Name | Value |
1/31/2016 | AB | 10 |
2/29/2016 | AB | 11 |
3/31/2016 | AB | 12 |
1/31/2016 | AA | 3 |
2/29/2016 | AA | 4 |
3/31/2016 | AA | 5 |
1/31/2016 | AC | 2.5 |
2/29/2016 | AC | 3.5 |
3/31/2016 | AC | 4.5 |
Table 2 | |
Name | Type |
AB | Cash |
AA | Stock |
AC | Cash |
Example only; Desired Result will be a measure, not a table | ||
Date | Name | Value |
1/31/2016 | Cash | 12.5 |
2/29/2016 | Cash | 14.5 |
3/31/2016 | Cash | 16.5 |
Solved! Go to Solution.
If you already have the relationship, then all you should need to do is to restrict the Type to Cash.
SumCash = CALCULATE ( SUM ( Table1[Value] ), Table2[Type] = "Cash" )
Hi @ARob198 ,
You can create a measure as below on the premise that Table 1 and Table 2 have created a relationship based on Name field:
SumofValue = CALCULATE(SUM('Table1'[Value]),ALLEXCEPT('Table2',Table2[Type]))
Best Regards
Rena
Hi @ARob198 ,
You can create a measure as below on the premise that Table 1 and Table 2 have created a relationship based on Name field:
SumofValue = CALCULATE(SUM('Table1'[Value]),ALLEXCEPT('Table2',Table2[Type]))
Best Regards
Rena
If you already have the relationship, then all you should need to do is to restrict the Type to Cash.
SumCash = CALCULATE ( SUM ( Table1[Value] ), Table2[Type] = "Cash" )
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |