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

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

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

Thanks for responding,

Here is the newest workbook with data model.

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

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?

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?

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"))

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.

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.

It worked! Outstanding, thank you very much sir!