cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryandevs
New Member

Issue with DAX calculation being correct at detail level, but not intermediary

Hello,

 

I have some calculations I'm working on in regards to breakeven bushels per acre. What I am seeing is that if I have a table showing data at the lowest level in a table visual, it works fine. The calculation is also correct at the total (all crops) level. It's a basic calculation of Total Expenses / Actual Harvested Acres. 

 

However, when I use a hierarchy and show the data in a summarized form, the calculation is doing something different. The calculation works at an individual field level, but when the data is in a different visual and shown at the crop level (multiple fields), I'm not sure how I need to change the calculation. I think I'm missing something in regards to getting just the subset records.

 

In my PBIX file, I have a few sample measures. 

 
BREAKEVENV3 =
VAR TEXP = [Total Expenses]
VAR AHA = SUM(Class[Actual Harvested Acres])
RETURN
DIVIDE(TEXP,AHA,0)

 

Breakeven Bushels Per Acre (v2) =
//stores what I think is total for the class
VAR CLASSHARVEST = sum(Class[Actual Harvested Acres])
RETURN
if(
ISFILTERED('GL Transactions'[Crop]),
DIVIDE([Total Expenses], CLASSHARVEST ,0),
DIVIDE([Total Expenses],
CALCULATE( SUM(Class[Actual Harvested Acres]),ALLSELECTED('GL Transactions'[Class])),0))
 

Hieararchy is:

Crop (Corn, Wheat, Soybeans)

Class (Individual field)

 

I have written this calculation a few different ways with similar results so I think it is a logic error I'm missing. 

 

The total level is correct (122109.4 Total expenses / 1320.1 Acres = 92.50008)

Both breakeven calculations are correct, see example for winter wheat:

B1: (27091.39 / 283.90 = 95.43)

B2: (5312.06 / 53 = 100.23)

 

Crop level incorrect:

Expected value for Winter wheat: 32403.45 / 336.9 = 96.18121

Actual value for Winter wheat: 24.55

 

Thanks for any suggestions. I've found numerous posts pointing to do a percentage of total calculation that needs to be adjusted, but I think my problem needs to be solved by ensuring I have the correct subset of records, which I think is where I am going wrong. 

 

Edit1: Here is an updated link that looks to work for me. 

https://ryandevsmore-my.sharepoint.com/:u:/g/personal/ryandevs_ryandevsmore_onmicrosoft_com/ESPQEsB7...

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
ryandevs
New Member

Hi @Ashish_Mathur, Thanks for taking the time to reply. It does indeed look like your suggestion resolved the issue. 

 

It appears the issue was that my hieararchy was returning different results versus individually dragging the different hieararchy values in. 

 

If this is by design or if there is any documentation to relect this, I'd love to know a little more. Otherwise I'm happy with the issue resolved. Thank you again!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ryandevs
New Member

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
AlexisOlson
Super User
Super User

FYI, I can't access the link you shared. You might want to adjust the permission settings.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors