locked
How to get a measure to match up with another measure RRS feed

  • Question

  • I have a table with 5 columns. ID, Type, Sub, Use, and Measure. 

    I put Type as a row label and sum of use as a measure for each type. The last column is called measure, and I want the measure to be summed BUT counting it under the type that was identified by use. This is a little confusing so spreadsheet attached. 

    https://www.dropbox.com/s/jxbk0n1y6780h0p/Book5.xlsx?dl=0

    Friday, January 6, 2017 10:36 PM

Answers

  • Hi Albo44,

    Based on your description in another thread, the redispatch with a "yes" status might not be second, but rather third or fourth. The measure calculations used in data analysis. Examples commonly found in business reports include sums, averages, minimum or maximum values, counts, or more advanced calculations. We are unable to get the corresponding record because it’s dynamic in measure. So it needs to a dynamic parameter, we are not achieve in PowerPivot. You can post your thread to Excel developer for professional support.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 12, 2017 7:22 AM

All replies

  • Your description of the problem is very difficult to understand (see attached). Please don't use 1 in every field, but values that make it possible to relate the desired output to the input:


    Imke Feldmann TheBIccountant.com

    Saturday, January 7, 2017 6:26 AM
    Answerer
  • Hi Imke, 

    Let me see if I can explain a bit better. 

    The ID is important because I'm trying to identify the "type" that I want to count for each ID (if anything "sub" is not necessary). That is what the "use" column does (I have a formula here that all a count for each "type...as shown in my desired result). The measure formula or we can call it "cash" is supposed to be looking in each ID to see if there is a value and it returns 1 or blank if there is nothing. I can easily put "type" on row labels and then a count or sum of that column as the measure but the problem is that it won't identify the correct "TYPE" (which is what the use column does). Let me recreate the spreadsheet to see if it makes more sense. 

    https://www.dropbox.com/s/oo6x706nzjielej/Book5.xlsx?dl=0

    Saturday, January 7, 2017 9:55 AM
  • I actually tried a solution by myself. Let me know what you think. My only concern is that it's too many calculated columns and my data set is humongouuuuuus. 

    https://www.dropbox.com/s/oo6x706nzjielej/Book5.xlsx?dl=0

    Saturday, January 7, 2017 10:18 AM
  • Hi Albo44,

    You too many calculated columns and my data set is humongous, you want to create measure to get the result rather than calculated columns, right? Or it runs slowly, you want to improve the efficiency? In your given attached file, there are only four columns. Please describe your issue clearly, so we can help you. Thanks for better understanding.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 9, 2017 3:17 AM
  • Yes I would like to create a measure to get that same result. My original data set is very large so adding 4 calculated columns is a lot of strain in terms of efficiency I thin. Thank you kindly. 
    Thursday, January 12, 2017 1:09 AM
  • Hi Albo44,

    Based on your description in another thread, the redispatch with a "yes" status might not be second, but rather third or fourth. The measure calculations used in data analysis. Examples commonly found in business reports include sums, averages, minimum or maximum values, counts, or more advanced calculations. We are unable to get the corresponding record because it’s dynamic in measure. So it needs to a dynamic parameter, we are not achieve in PowerPivot. You can post your thread to Excel developer for professional support.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 12, 2017 7:22 AM