Maggie Barr
Board Regular
- Joined
- Jan 28, 2014
- Messages
- 188
Hello and thank you in advance if you can help,
I am using excel 2019 on a desktop, not Power Bi.
I had posted this in the Power Bi section of this forum, but I think now, because I am not using power Bi but Power Pivot in excel 2019, I should have posted it here.
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
I am using excel 2019 on a desktop, not Power Bi.
I had posted this in the Power Bi section of this forum, but I think now, because I am not using power Bi but Power Pivot in excel 2019, I should have posted it here.
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
