Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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' :
Category | CategoryNumber | Amount | Formula it is supposed to have | |
SalaryCosts | 1 | -100 | A | |
SalaryCosts | 1 | -500 | A | |
SalaryCosts | 1 | -900 | A | |
SalaryCosts | 1 | -200 | A | |
Revenue | 2 | 3600 | B | |
Revenue | 2 | 18000 | B | |
Revenue | 2 | 32400 | B | |
TravelCosts | 3 | -5 | C | |
TravelCosts | 3 | -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':
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
Solved! Go to Solution.
// 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
// 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 Great, thanks! I with both of your answers combined I will try to proceed building!
The first steps I made look promising
@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/
@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:
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:
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:
Thank you in advance!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |