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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Apply different formulas with parameters to different rows

Hi Everyone!

Currenly I am in need of some help regarding the subject mentioned above.

I have the following example dataset which is named 'Totals' : 

CategoryCategoryNumberAmount Formula it is supposed to have
SalaryCosts1-100 A
SalaryCosts1-500 A
SalaryCosts1-900 A
SalaryCosts1-200 A
Revenue23600 B
Revenue218000 B
Revenue232400 B
TravelCosts3-5 C
TravelCosts3-25 C

 

The formula's that I hope to get linked to certain rows are as followed:

Formula A --> Adding Employees which causes an increase in employee related costs but not in revenue

Average(Totals[Amount]) * (Amount of employees + Parameter for more/less employees)

 

Formula B --> Adding orders which causes an increase in revenue but not in employee related costs

Average(Totals[Amount]) * (Amount of orders * Parameter % change for more/less orders)

 

Formula C --> Totals that are not influenced by the amount of employees/orders

SUM(Totals[Amount])

 

Eventually my goal is to plot a graph, and when you for example select 'SalaryCosts' the formula with the parameter gets plotted

 

I tried this in a Power BI measure, but it says it cannot find 'CategoryNumber': 

VAR A = Average(Totals[Amount]) * (Amount of employees + Parameter for more/less employees)
VAR B = Average(Totals[Amount]) * (Amount of orders * % change for more/less orders)
VAR C = SUM(Totals[Amount])

Return

IF(Totals[CategoryNumber]= 1; A;
IF(Totals[CategoryNumber]= 2; B;
IF(Totals[CategoryNumber]= 3; C; C)))

 

I hope someone can help me out!
P.S. It is my first post, please don't hesitate to tell me how to make a post more clear

1 ACCEPTED SOLUTION
Anonymous
Not applicable

// Within measures you can't use naked columns,
// you have to wrap them in some kind of aggregation.
// Of course, this does not apply to iterators.
// Change the last bit of your formula (after return) to:

switch ( SELECTEDVALUE( Totals[CategoryNumber] ),
	1; A;
	2; B;
	C
)

However, this formula will (most likely) eagerly calculate all the measures, even though they are not needed. Please move the calculation of A, B, and C under the SWITCH to make it faster.

 

Best

D

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

// Within measures you can't use naked columns,
// you have to wrap them in some kind of aggregation.
// Of course, this does not apply to iterators.
// Change the last bit of your formula (after return) to:

switch ( SELECTEDVALUE( Totals[CategoryNumber] ),
	1; A;
	2; B;
	C
)

However, this formula will (most likely) eagerly calculate all the measures, even though they are not needed. Please move the calculation of A, B, and C under the SWITCH to make it faster.

 

Best

D

Anonymous
Not applicable

@Anonymous  Great, thanks! I with both of your answers combined I will try to proceed building! 
The first steps I made look promising

amitchandak
Super User
Super User

@Anonymous ,

refer: https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@amitchandak,Thank you for the reply.

 

As a rookie, it took me some time to figure out how to apply the formulas in the right way on my own dataset.

When I use the first link you posted it gives me the nice layers to drill down on which are useful, thank you for that.

Unfortunately I didn't find to find my exact solution yet.


Taking the link as example
In the example they use 'Country' as a layer, within that layer you have 'France, 'UK' and 'USA'.

On that layer I want to apply different formulas one for 'France', one for 'UK' and one for 'USA'.

Right now I only see: 

ISINSCOPE(sellers[Country]); CALCULATE([Sum of Value]; Sellers[IsTotal]=1; ALLEXCEPT(Sellers;Sellers[COUNTRY]))
Which seems like 'Sum values when it is in the country layer and it is a total', but not country/row specific

Is this possible at all?

Thanks!
Pragati11
Super User
Super User

HI @Anonymous ,

 

What is the datatype of the column CategoryNumber  in your table? Is it Text or Number?

If it is text format, then modify part of your DAX as follows:

IF(Totals[CategoryNumber]= "1"; A;
IF(Totals[CategoryNumber]= "2"; B;
IF(Totals[CategoryNumber]= "3"; C; C)))
 
NOTE: If it still gives error try creating a COLUMN rather than MESURE!
 
If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂
 
Thanks,
Pragati

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

Hi @Pragati11 ,

 

Thank you for your response!
The datatype of the column CategoryNumber is a Number

 

Regarding your Note:

Does using a column instead of measure still give me the capability to keep it 'dynamic'?

I would like to use the formula's that are listed in VAR A,B and C to plot a graph and then see what the effects are(how the graph changes) when I slide the parameter for adding the employees/orders.

 

 

In addition, I just figured out to upload a picture in my post.

It currently shows this from my dataset(sorry for the different language, but it is from my original set.

With the message: 

" Cannot find name '[Productgroup]'."

 

Thank you in advance!

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.