Can't get new measure based off other measures in Power pivot to work, please help.

[Maggie Barr]

Hello and thank you in advance if you can help,
I am using excel 2019 on a desktop, not Power Bi.

I am trying to create a measure in power pivot that, despite repeated attempts, is not working. I have, in other scenarios, divided one measure by another to get a % I needed (or the decimal), but in this case, it is just returning a “1” in each cell in the pivot. I am trying to create a % calculation for each species according to a breeding category within a Region & BlockType. I created a measure that provides me the distinct count of the number of species within each breeding category (Confirmed, Probable, and Possible) for each Region & BlockType, and another measure that provides me the distinct count of the number of species for each Region and BlockType in total. In the past, I could just divide one measure by the other, and once in the pivot table change it to a percent, but this one just won’t work.

Below is the formula for the other measures I created that work fine (measure name is underlined):

Region; BlockType % Blocks Completed = BrdingBird_Blocks_Completed_Summary[Sum of # Blocks Completed]/BrdingBird_Atlas_Blocks_Full_Working[Region; BlockType TOTAL # Blocks]

Region; BlockType % Blocks w/Data = Data_All_Records[Region; BlockType # Blocks w/Data]/BrdingBird_Atlas_Blocks_Full_Working[Region; BlockType TOTAL # Blocks]

BUT, the one I can’t get to work is:

Region; BlockType % of species in breeding type = Data_All_Records[Region; BlockType # Species Recorded by breeding category]/Data_All_Records[Region; BlockType # Species Reported]

I am afraid I don’t know how to word this better, but if anyone has any idea why I am only getting a “1” in the pivot field versus the decimal I should get, please advise.
Thank you for your time if you can help.

The files are large and I cannot share, but here is a link for a small chunk of the pivot output (3 actually) in a word document with some highlights to see what I am talking about.

https://app.box.com/s/u1dkibvn4i3b41atoskcdlfu0ge7567s

Sincerely,
Maggie

 

[macfuller]

Always glad to help the birds.

First, I assume you have formatted the measure as a % and taken it out to enough significant digits that you're sure it's returning 100% as opposed to 95%+.

Second, I assume you have split the numerator and denominator measures and reported them in your pivot table, and that they are returning the same (non-zero) amount such that the measures are the same numeric value, thus dividing it is = 1.

If the 2nd assumption is true, you might suspect that your filter context is affecting your denominator as well as the numerator. Since you are attempting to isolate the breeding category in your numerator it might be that when you have a row context of breeding category (I gather from the link the values are CONF, PROB, POSS) the denominator is also filtered. Perhaps you can put an ALL(Breeding Category) clause into your denominator measure and see if that works.

 

[Maggie Barr]

macfuller,
Thanks so much for getting back to me.
I actually found that if I make my measures in % format, when I bring them into a pivot table, they are not formatted correctly, and I got some "wonky" numbers, but if I leave them as a general format, I can just bring them into the pivot and format them as percent there and they were correct. Likely a problem on my end, but how I was doing it worked.

I think you have figured out the problem, now I just have to figure out the remedy. In my table in word, for Allagash Regular, you can see 22 records for CONF, and in the table above the measure for the total number of species recorded in Allagash Regular of 78. That measure reports 78 distinct species for the total, but I think you are right, that the new measure is breaking it down into the same groups for the measure by breeding category number (the numerator). At least that is what makes sense now that you pointed it out. However, I cannot us an ALL for the breeding category because species numbers are repeated, meaning that there can be the same species in each category, and to get the correct % it has to compare the number in each category to the distinct count of the species in the Region BlockType, which my denominator measure calculates. If I used your ALL feature, it would use 101 as the sum of all breeding category rather than the 78 distinct species. I just find it odd that it can’t reference from the unique category in the breeding category (numerator) to the overall stored number for the Region BlockType (denominator). I even made sure that the pivot table is based off one query to make sure it wasn’t a relationship issue. I could likely create a series of queries with groupings and sum/count features to make all the variables need and merge them together to do the calculations and use that merged query for the base of the pivot, which is what I did before learning measures, but that is tedious, messy, and ultimately slows the workbook down considerably.

Do you know of a way that I could force the measure to consider the denominator as the whole number that it calculates and not have it somehow broken down into groups as in the numerator?
Thank you so much for your help, the birds and the bird geeks greatly appreciate it,
Maggie

 

[macfuller]

Without knowing the exact fields it's hard to say how to ignore the breeding category-species selections from the filter. Perhaps you can create a combined field of breeding category + species and that can be in your ALL() clause. You can also put multiple ALL() statements in your denominator measure. It sounds like you want a category in the numerator but not the denominator which is kind of a reverse from the usual .

Try making a simple table of what values you want in the percentage and that might help you figure out how to manipulate the filters. Not that this is correct, but...

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Numerator[/TD]
[TD]Denominator[/TD]
[/TR]
[TR]
[TD]Breeding category[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Species[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Region[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

 

[Maggie Barr]

macfuller,
You are right about the merged fields. I have already created a series of merged fields I use that let me create my DISTINCTCOUNT measures. For the numerator I created a measure that is DISTINCTCOUNT of the merged field of "Common Name; Breeding Category; Region; BlockType". For the denominator I created a measure that is DISTICNTCOUNT of the merged field "Common Name; Region; BlockType". Both of these measures are working flawlessly. My problem is that the measure for the numerator is a larger grouping, returning three values for each Region BlockType, while the denominator is producing one value for each Reagion BlockType. I think what is happening, which you pointed out, is that the grouping in the measure for the numerator is somehow affecting the grouping in the denominator. I have tried multiple different ways to do this, CALCULATE, DIVIDE, DIVIDE DISTINCTCOUNT etc. All things I have found online, and either I am doing it wrong or it won't work. The one I thought might be promising was not using the measures but =DIVIDE(DISTINCTCOUNT(Data_All_Records[CommonName; BreedingCategory; Region; BlockType]),DISTINCTCOUNT(Data_All_Records[CommonName; Region; BlockType])) that I had seen in a POWER BI website, and it still comes back as 1.
Thanks,
Maggie

 

[Maggie Barr]

Macfuller,
Well, I didn’t get to accomplish my goal with a measure, but I did get it done the “dirty” way. I made two reference queries off my full data query. In the first I did a series of strategic groupings until the final grouping would provide me with a count of the # of species in each Breeding Category by Region; BlockType (186 rows). The second I did some strategic grouping until the Count provided me with the total # of species within each Region; BlockType (62 rows). I then referenced the first query to create a new query, and merged that to the second query based on the “Region; BlockType” column in both, bringing in the total # species for the Region; BlockType. I then created a custom column dividing the “# of species in each Breeding Category by Region; BlockType” BY “total # of species within each Region; BlockType”. I then added the query to the data model, made a relationship between that and the ArcMap locations file that I use for my headers in the columns of the pivot (I do this so if there are locations not accounted for in the dataset you still see the locations), put the breeding category as the rows, and brought in both fields, and they work perfectly.

I know your assessment of the fact that the grouping in the numerator is affecting the grouping in the denominator, but I just could not figure a way to make the measure apply the total from the denominator to the numerator base on the shared feature of the “Region; BlockType”. It isn’t the prettiest way, but it works. That being said, if you ever have any ideas on how I can force the measure to do what I need, I would love to hear from you. Having three queries in a workbook to do what one measure should isn’t a best management practice I am sure, but the learning curve sometimes is too steep, so you hike around the mountain, you still get there, but it takes a lot longer.

Thank you again for all your help, I truly appreciate it!!!
Best Wishes,
Maggie

 

[Maggie Barr]

Macfuller,
Last update, but I felt I should post it.
So, I am a humble person in general, but it is with great humility that I share how I finally did this. As it turns out, I placed the common name of the species in the values section of the pivot and used the Distinct Count function there (I had not known that even existed before). I then placed the common name in again as distinct values, but then went to the show values as tab and used the % of parent row and selected the level of grouping (State, State & Block Type, or Region & Block Type) in the Base field section and “viola” there it was. I then moved values to rows so the formatting was better. Sometimes you get so immersed in queries and measures that you forget to step back out and look at things from the broader perspective. This meant that I could delete the 9 queries I had developed to create this for the three grouping types.
Thanks for your help, sorry for the bother.
Maggie