Can a Measure create more than one value?

[Maggie Barr]

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

 

[James006]

Hello,

Seems that you Pivot Table needs some grouping and a few calculated fields ...

see : https://www.contextures.com/excel-pivot-table-calculated-field.html

Hope this will help

 

[Maggie Barr]

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

 

[James006]

Hello,

To count Unique items ...

https://www.contextures.com/pivottablecountunique.html

Hope this can help

 

[macfuller]

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?