Forum Discussion
Calculating a sum when a Column header contains a value
Hey guys, i am just wondering if it's possible to create a dax formula that will do a sum, and another that can count the values >0 for any column header that contains the word "Month".
for example,
| Name | Location | Type | Month 1 | Month 2 | Month 3 | Month 4 | etc… | Sum | Month Count |
| A | x | i | 32 | 39 | 10 | 0 | ... | 81 | 3 |
| B | y | j | 31 | 43 | 40 | 49 | ... | 163 | 4 |
| C | z | k | 34 | 19 | 0 | 0 | ... | 53 | 2 |
6 Replies
Hi aar0n,
For doing this the best way is to unpivot the columns month and make a calculated measure based on context for the count:
Count = CALCULATE(COUNT(Months_Data[Value]) ; Months_Data[Value] > 0)
Then just add the Attribute column to the columns and the values and created measure to values and reduce the size of the count measure in the matrix in order to hide it from the totals:
Regards,
Mfelix
Thats a really clever way to do it.. the only issue is that i need the actual values for some other calculations! so i need the values themselves.
Hi aar0n,
Not really sure what you mean by you need the actual values itself, unpivoting the columns only change the way the data is saved on the database, you aren't changing any of the values just putting them on columns to use on a different way you can still calculate measures and make visuals based on them you are not makning any data transformation.
Regards,
MFelix
