Forum Discussion
Dax PowerPivot Adding Measure
Hi, need help with creating measure
i'm trying to create a measure in powerpivot to add total of a measure to another measure. For example, in below table
i want to add total of Internal measure (with is 20 in this case) to Owned only for RSD.
| Owned | Internal | |
| IT | 20 | 10 |
| WPS | 20 | 10 |
| RSD | 10 |
so, my new measure should look as per below table. (Final measure).
| Owned | Internal | Final | |
| IT | 20 | 10 | 20 |
| WPS | 20 | 10 | 20 |
| RSD | 10 | 30 |
Hi prash4030,
Measure is already an aggregation value so I'm afraid you can't aggregation measure again. As a workaround, you may convert measure [Internal] to calculate columns, than create a calculate columns using DAX formula below:
Final = VAR total = SUM('Table'[Internal]) VAR Own_RSD = CALCULATE(SUM('Table'[Owned]), FILTER(ALL('Table'), 'Table'[Category] = "RSD")) RETURN IF('Table'[Category] = "RSD", total + Own_RSD, total)You may also refer to the sample pbix file.
Regards,
Jimmy Tao
3 Replies
Hi prash4030,
Measure is already an aggregation value so I'm afraid you can't aggregation measure again. As a workaround, you may convert measure [Internal] to calculate columns, than create a calculate columns using DAX formula below:
Final = VAR total = SUM('Table'[Internal]) VAR Own_RSD = CALCULATE(SUM('Table'[Owned]), FILTER(ALL('Table'), 'Table'[Category] = "RSD")) RETURN IF('Table'[Category] = "RSD", total + Own_RSD, total)You may also refer to the sample pbix file.
Regards,
Jimmy Tao
thank you so much. it worked. thank you
Hi prash4030
I would need to see the code for your measures [Owned] and [Internal] but if I make a guess on what they look like, let's try this:
Final = CALCULATE ( [Internal], All[Table1] ) + CALCULATE ( [Owned], KEEPFILTERS ( Table1[FirstField] = "RSD" ) )where Table1 is the name of the table with your data and FirstField is the name of the column containing "RSD", "IT", etc.
Always show the structure of your tables with names so it is easier for people to help.
