Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
maibacherstr
Helper III
Helper III

Calculating Confusion between Measures and Columns. What If Parameter is in use

Hello community, I'm working with my first What If Parameter which is certifiably awesome, but it requires use of measures that appear to be making it difficult to perform some follow-on calculations. For example, I have a ProposedBudget measure that simply takes the input from the parameter, which is a whole number ($). Next, I want to determine the MaxQuantity of equipment units that can be purchased given the ProposedBudget:

 

Measure: MaxQuantity = ProposedBudget / average([CostPerUnit])

Where ProposedBudget is a measure and CostPerUnit is a column

--> Since MaxQuantity should be a whole number rounded down, it's actually ROUNDDOWN([ProposedBudget] / AVERAGE ([CostPerUnit]),0). Please refer to the screenshot below where the first row in my table looks like it's calculating correctly, but every other row value = "NaN" (not a number, maybe dividing by zero).

 

Here's where problems start to become overwhelming:

 

Next, I want to calculate the TotalCost (Column) using the ProposedBudget, which I've written as: MaxQuantity * CostPerUnit. The values are outlandishly high.

Last, I want to calculate the TotalCostRemainder (Column) by using: ProposedBudget - TotalCost. This column is incorrectly returning zero.

 

The worst part is, I already got all of this working because in my first attempt setting up the What If Parameter, I used the parameter as a % and applied it as a multiplier. Now, I'm simply creating a new report page with a new (but extremely similar) setup that uses a newly added parameter as a whole number in budget $. My original report page still works perfectly...

 

 

maibacherstr_0-1683567623254.png

 

Do the esteemed experts have any advice? THANK YOU

4 REPLIES 4
maibacherstr
Helper III
Helper III

@tamerj1 

 

Thanks again for jumping in last week. The formula using ROUNDDOWN appears to be the cause why my visual is locking up. (Example: the formula is for the max quantity of equipment items (by type such as widget 1, widget 2...) that we can afford given a proposed budget. The equipment types in my visual will no longer filter when this formula is applied).

 

Oddly, this was supposed to be the easy example bc my report already uses a similar What If parameter which I thought would be more difficult, but is working properly.

What If 1: uses the parameter as a % multiplier to an existing baseline budget, giving us the 'proposed' budget (works without issue)

What If 2: uses the parameter as a $ amount that acts as the new 'proposed' budget (causing issues)

 

I set up everything as carefully as possible for What If 2 to mirror What If 1; in fact the entire report page (tab) is exactly the same. At this point everything that can be written as a measure is written as a measure.

 

Given my OP and discussion on "MaxQuantity", does anything stand out? I've been troubleshooting this for days.

 

Many thanks again

@maibacherstr 

Let's try to connect this week to go through it and try to understand this behavior. 

tamerj1
Super User
Super User

@maibacherstr 

Please try

=
ROUNDDOWN (
[ProposedBudget]
/ CALCULATE ( AVERAGE ( 'Table'[CostPerUnit] ), ALLSELECTED () ),
0
)

GM, the formula worked in no time yesterday, THANK YOU. Then I cleaned up the rest of the calculated set writing everything as measures, definitely the way to go, but some unexpected behaviors nonetheless occurred and I'm re-testing your formula in the context of everything else. Thanks again

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors