none
Power Pivot Measure not calculating correctly, please help RRS feed

  • Question

  • Hello and thank you in advance if you can help,

    I am using Excel 2019 on a PC desktop.  I have created measures that divides one previously calculated measure by another previously calculated measure.  I had done this flawlessly several times for other needs, but my most recent attempt failed and returned only the number 1.  Both measures are developed and stored in the same query. This is hard to describe, so please be patient as I try.

    The measure that worked before followed this format:

    Table: Data_All_Records

    Measure Name: Region; BlockType % Blocks w/Data

    Formula: Data_All_Records[Region; BlockType # Blocks w/Data]/BrdingBird_Atlas_Blocks_Full_Working[Region; BlockType TOTAL # Blocks]

    What it does:  This takes the measure of # of blocks w/data and divides it by the total number of blocks from the Winter Blocks full file.  The Region; BlockType # Blocks w/Data was a DISTINCTCOUNT measure as was the Region; BlockType TOTAL # Blocks.

    The one that is not working is:

    Table: Data_All_Records

    Measure Name: Region; BlockType % of species in breeding type

    Formula: Data_All_Records[# Species Recorded by breeding category]/Data_All_Records[Region; BlockType # Species Reported]

    What it does:  Here you are trying to get the percent of species that are confirmed, possible, or probable.  The first measure reference was a DISTINCTCOUNT of a field with "CommonName; BreedingCategory; Region; BlockType" & the second (denominator) was a measure of DISTINCTCOUNT  of a filed with "CommonName; Region; BlockType".

    I think it has something to do with the fact that the numerator measure is recording 186 rows of data, while the denominator is recording 62 rows of data, and somehow perhaps the grouping in the numerator will only draw the results of the second measure to the same level of grouping.  They are related as they both contain the Region; Blocktype field.

    I have accomplished what I needed to do with two queries off the full data file and then merging them to bring in the data for the 62 row query into the 186 row query and doing a custom column for the calculation, but because I have to do this with my data at three levels, State, State & BlockType, and Region & BlockType, I had to create 9 queries to accomplish what I need for three pivot tables.

    I have what I need, but I really feel like the measure should work, and doing it all through queries makes for a cluttered workbook etc.  

    Any Advice would be greatly appreciated.

    Thank you,

    Maggie

    Thursday, May 2, 2019 4:02 PM