Ch 16 Creating a Harvester Calculated Field

A dedicated forum to help people when working through the book "Learn to Write DAX"
Kelly
Posts: 1
Joined: Wed Oct 05, 2016 10:48 pm

Ch 16 Creating a Harvester Calculated Field

Postby Kelly » Wed Oct 05, 2016 11:02 pm

I have followed all of the steps to create the Harvester for % increase but my Total Margin with Selected Increase always calculates a 15% increase--so it is not "harvesting" the selected value. Any ideas?
Harvester.JPG
Harvester.JPG (49.57 KiB) Viewed 6184 times


[Selected Value]=MAX (Increase[Value])

[Total Margin with Selected Increase]=[Total Margin $] * (100 + [Selected Value])/100

MattAllington
Posts: 1159
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Ch 16 Creating a Harvester Calculated Field

Postby MattAllington » Sun Oct 16, 2016 3:33 pm

Hi Kelly, sorry I missed your post Last week. Everything I can see looks correct. Is the slicer connected to the pivot table? My guess is that it is not.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Sarmyles
Posts: 6
Joined: Mon May 30, 2016 7:02 pm

Re: Ch 16 Creating a Harvester Calculated Field

Postby Sarmyles » Tue Jan 17, 2017 4:11 pm

Matt,
I had the same problem and your solution is correct - the pivot table was not connected. Why does this need to be done manually ?

MattAllington
Posts: 1159
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Ch 16 Creating a Harvester Calculated Field

Postby MattAllington » Tue Jan 17, 2017 8:17 pm

If you first select a pivot table, then add a slicer (right click on the column in the field list and select "add as slicer") it will automatically be connected. If you have not selected the pivot table and you "insert slicer" from them menu, it will not be connected and you will have to connect it manually. It makes sense actually. If here are 5 pivots in a page and you add a slicer, which ones should be attached? It depends of course and his way you have control.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

alex
Posts: 1
Joined: Sat Sep 07, 2019 6:27 am

Re: Ch 16 Creating a Harvester Calculated Field

Postby alex » Sat Sep 07, 2019 6:32 am

Hello, I have exactly the same problem shared by Kelly,
The slicers are ok, formulas as well, but the Total Margin with selected Increase is not getting updated although I change the Selected Value.
Is there any solution for this issue found?
Do appreciate the help.

Jobahead
Posts: 1
Joined: Mon May 04, 2020 5:06 am

Re: Ch 16 Creating a Harvester Calculated Field

Postby Jobahead » Sat Apr 24, 2021 12:22 pm

I too faced the same issue. Resolved by modifying a few steps based on Matt's Practice Exercise 70 in his book Supercharge Excel.
Modified Steps
1. Create a list of possible values 1 to 15 with header Value
2. CTRL+L or CTRL+T to create Excel table
3. Rename Table as Increase
4. Select any cell in the table, go to the Power Pivot Tab and select Add to Data Model
5. Do not link this table to any other
6. Create a Pivot table with
a. Product Category (in Rows)
b. Calendar Year (in Filter)
c. Total Sales (in ∑Values)
d. Total Margin (in ∑Values)
7. Select this pivot table and create a Measure
Selected Value = MAX(Increase[Value]) ([color=#4040FF]Do not create a separate Pivot table for Value)[/color]
8. On the Field List, right click on the Value field, and “Add as Slicer” ([color=#4040FF]Do not add the slicer from the Excel ribbon (Insert -> Slicer)[/color]


9. Select this pivot table and create the measure to show the changed Total Margin$
Total Margin with Selected Increase = [Total Margin$]*(100 + [Selected Value])/100

The values should now change based on the Slicer selection.


Return to “Help with the Book "Learn to Write DAX"”

Who is online

Users browsing this forum: No registered users and 3 guests

cron