+ Reply to Thread
Results 1 to 15 of 15

Powerpivot report

  1. #1
    Registered User
    Join Date
    02-20-2017
    Location
    Cape Town
    MS-Off Ver
    Office Proplus 2016
    Posts
    8

    Powerpivot report

    Hi All,

    I recently acquired power pivot to analyse large amount of data generated in field trials conducted for registration of crop chemicals. The plan is to build foundation for dynamic reporting of various key stages of the process.

    I managed to organise data in several data and lookup tables, which I then uploaded to powerpivot data model. However, when I create the various pivot reports, rows for certain fields are replicated. It seems the relationships are not setup correctly but I cannot figure out exactly what the problem is.

    I uploaded a sample dize document of a couple of projects called 'master data', and 'reports' which are suppose to be linked. Is it possible for someone with experience in creating dashboards and such with power pivot could advise?

    Much appreciated
    Attached Files Attached Files

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,084

    Re: Powerpivot report

    Hi,

    I think it would be helpful if your example contained the data model as you set it up and an example of the pivot tables that do not work properly.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    02-20-2017
    Location
    Cape Town
    MS-Off Ver
    Office Proplus 2016
    Posts
    8

    Re: Powerpivot report

    Hi,

    Thanks, previously file was to big to upload. Please find sample for both source data in xcel spreadsheets and pivot reports with data model attached.

    I kept trying and eventually found a way to report data without repetition. I would appreciate if you can check if I set it up correctly?

    The next problem occurred while trying to add the data ('pest incidence') to the value field for a second time in order to calculated the percentage difference from the control/untreated. It keeps giving me the #N/A error. I figured it could be a data format problem, could you perhaps advise how to fix this?

    THanks
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-20-2017
    Location
    Cape Town
    MS-Off Ver
    Office Proplus 2016
    Posts
    8

    Re: Powerpivot report

    Hi again,

    Apologies for repeated questions - I really am battling to get to grips with measures in power pivot.

    In the attached workbook, I managed to use a measure to calculate the percentage control (difference) between treatments. However,the results in xcel are wrong. Could you advise how to fix this by any chance?

    Thanks
    Attached Files Attached Files

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Powerpivot report

    I don't see data model in your workbook.

    Can you upload workbook with data model? Along with your measure that's giving you wrong result and manually calculated correct result.

    EDIT: Ah I had missed the previous post which had 2 attachments. Taking a look at it.
    Last edited by CK76; 02-22-2017 at 08:29 AM.

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,084

    Re: Powerpivot report

    Hi,

    I do not have access to Excel 2016 during the day but will try and find the time to have a look at your workbook tonight. If you are pressed for time, I might suggest you post- adding appropriate links back here- to the PowerBI forum at MrExcel. Many of the PowerBI heavyweights seem to frequent that forum.

  7. #7
    Registered User
    Join Date
    02-20-2017
    Location
    Cape Town
    MS-Off Ver
    Office Proplus 2016
    Posts
    8

    Re: Powerpivot report

    Thanks for responding,

    Here is the newest workbook with data model.

    If will also post on MrExcel forum as suggested, thank you.
    Attached Files Attached Files

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Powerpivot report

    Drat. Forgot that I need Excel 2016 Pro Plus for Data model based PowerPivot.

    What is DAX formula you are using for the measure currently?

  9. #9
    Registered User
    Join Date
    02-20-2017
    Location
    Cape Town
    MS-Off Ver
    Office Proplus 2016
    Posts
    8

    Re: Powerpivot report

    Firstly, I created a calculated column named AVE ***. with formula =([***. 1] + [***. 2] + [***. 3])/3

    Then proceeded with the following separate measures in the calculated field:

    PESTINCIDENCE:=AVERAGE([AVE ***.])

    UNTREATED:=CALCULATE(AVERAGE(DATA_INCIDENCE[AVE ***.]), TREATMENT[DESCRIPTION] = "CONTROL")

    TREATED:=CALCULATE([PESTINCIDENCE], (TREATMENT[DESCRIPTION] <> "CONTROL"))

    % CONTROL_TRE:=DIVIDE([TREATED], [UNTREATED] -1)

    Hope this helps?

  10. #10
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Powerpivot report

    Ok, try modifying UNTREATED measure like below.

    =CALCULATE(AVERAGE(DATA_INCIDENCE[AVE ***.]),FILTER(ALL('TREATMENT'),TREATMENT[DESCRIPTION] = "CONTROL"))

    % CONTROL_TRE should be like...
    =DIVIDE([UNTREATED]-[TREATED],[UNTREATED])

    EDIT: FILTER context for UNTREATED may need additional filter, using TRIAL ID.
    Last edited by CK76; 02-22-2017 at 10:19 AM. Reason: See Edit:

  11. #11
    Registered User
    Join Date
    02-20-2017
    Location
    Cape Town
    MS-Off Ver
    Office Proplus 2016
    Posts
    8

    Re: Powerpivot report

    I just want to make sure I understand correctly, If I use measure for UNTREATED below like you suggested with TRIAL ID filter I get a error:

    UNTREATED:=CALCULATE(AVERAGE(DATA_INCIDENCE[AVE ***.]),FILTER(ALL(DATA_INCIDENCE),TREATMENT[DESCRIPTION], TRIAL[TRIAL ID]= "CONTROL"))

  12. #12
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Powerpivot report

    Hmm, sorry I don't think I can help you further without looking at how your table relationships are built.

    To give you idea of how it's typically done...
    Lets say you have Revenue Table and DimRep Table, which are related by Long Names.

    Revenue Table
    Long Name Date Amount
    John Cougar Mellencamp 1/1/2017 1000
    John Cougar Mellencamp 2/1/2017 500
    John Cougar Mellencamp 3/1/2017 750
    Archer Malory Sterling 1/1/2017 800
    Archer Malory Sterling 2/1/2017 1600
    Archer Malory Sterling 3/1/2017 900

    DimRep
    Long Name Reps
    John Cougar Mellencamp Rep M
    Archer Malory Sterling Rep S

    Measures
    AvgAmt:=AVERAGE(Revenue[Amount])
    AvgAmtM:=CALCULATE([AvgAmt],FILTER(ALL('Revenue'),RELATED(DimRep[Reps])="Rep M"))

    AvgAmtM will return AvgAmt for "Rep M" from entire Revenue table regardless of PivotTable Row Label(s) or filters.

  13. #13
    Registered User
    Join Date
    02-20-2017
    Location
    Cape Town
    MS-Off Ver
    Office Proplus 2016
    Posts
    8

    Re: Powerpivot report

    Thank you, your explanation makes sense, the measure works and reports correctly but the result of the calculation is still wrong.

    I suspect it is because UNTREATED is calculated over all PESTINCIDENCE and not only for the particular TRIAL ID. The effect is that % CONTROL for untreated (:=PESTINCIDENCE/UNTREATED-1) is not 0.

    Pity I cannot share the data model, thanks again.

  14. #14
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,084

    Re: Powerpivot report

    I think this may work-
    Add a new measure called ALL_UNTREATED using
    =CALCULATE(AVERAGE(DATA_INCIDENCE[AVE ***.]), TREATMENT[TRADE NAME]="UNTREATED",TREATMENT[DESCRIPTION] = "CONTROL")
    Now amend your % CONTROL_TRE measure to this
    =if(COUNTROWS(DATA_INCIDENCE)>0,1-DIVIDE([PESTINCIDENCE], [ALL_UNTREATED]),blank())

    You may need to make it -1 rather than 1- depending on how you are looking at the percentage difference.

  15. #15
    Registered User
    Join Date
    02-20-2017
    Location
    Cape Town
    MS-Off Ver
    Office Proplus 2016
    Posts
    8

    Re: Powerpivot report

    It worked! Outstanding, thank you very much sir!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Allow use of Slicers on PowerPivot to be used by users without PowerPivot
    By weeble33 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-09-2017, 05:19 PM
  2. Replies: 4
    Last Post: 07-23-2015, 03:35 PM
  3. Replies: 2
    Last Post: 06-02-2014, 09:34 AM
  4. [SOLVED] PowerPivot reports with consolidated report from two tables.
    By nsr1989 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 12-18-2013, 06:42 PM
  5. Replies: 0
    Last Post: 07-25-2013, 03:01 PM
  6. PowerPivot - Asymmetrical report
    By Christian1977 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-22-2013, 07:40 AM
  7. Replies: 0
    Last Post: 11-20-2012, 05:34 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1