Can a Measure create more than one value?

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
188
Hello and thank you in advance if you can help,
I am VERY new to using measures. I am using Excel 2019 on a PC.

I have created some measures that will give me a distinct count of species in a query/dataset, as well as a distinct count of blocks. My data has fields/columns for All Regions, Region, BlockType, Block Name, and Species Name.

I have been searching for a way to see if I can create measures based on groups within the same table. For instance, I need a measure of the number of Block Names each species occurs in grouped by the Region and Block type so that when I create my pivot table I can put that in a column as it relates to each species in the row.

I have been able to do this using a number of reference queries by grouping and counting and summing numbers as needed, but the number of queries I created is almost mind boggling, and it is making my workbook run very slow as it progresses, so I am trying to find a way to do some of it with measures.

Can a measure store information as a series of numerical values for each group within a larger dataset, or do you have to create a query that groups the information first?
Thank you, any advice is appreciated,
Maggie
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
James006,
Thank you for the input, but I had looked into that before, and the calculated fields section is always grayed out. I think it is because I am working off of linked tables, so that feature is not available to me in my pivot table. I really don't want to have to load all my queries to tables in the workbook. If you think I am wrong and there should still be a way for me to utilize that, please let me know. I am still playing with the measures and making progress, but grouping be two columns and calculating a DISTINCTCOUNT function within the measure hadn't been resolved yet.
Thank you,
Maggie
 
Upvote 0
Maggie -

When you say you have created measures, can you post the DAX? You're title for this thread is significant - a measure creates a single value kinda sorta, but depending on the filter context it creates a value for each row or column in the filter context. You can perform a calculation such as SUM or Count for categories within a group. I realize this is greek to you right now, but can you give specific examples?
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top