Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aar0n
Advocate II
Advocate II

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,

NameLocationTypeMonth 1Month 2Month 3Month 4etc…SumMonth Count
Axi3239100 ...813
Byj31434049 ...1634
Czk341900 ...532
1 ACCEPTED SOLUTION

Hi @aar0n,

 

Though that you needed to have it all in your table then you can use a Table visual and just have the totals without hiding the numbers:

 

Table.png

 

The measures are calculated in context so it will work the same way.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

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:

Unpivot.gif

 

matrix.gif

 

Regards,

Mfelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



I Just mean that in your matrix visual, youre hiding the individual counts and leaving the column sums. 

i only need the total value.

Hi @aar0n,

 

Though that you needed to have it all in your table then you can use a Table visual and just have the totals without hiding the numbers:

 

Table.png

 

The measures are calculated in context so it will work the same way.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.