Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have the following table:
Sale_ID | Unity_Price | Tax_1 | Tax_2 | Tax_3 | Tax_4 |
1 | 10 | 5 | 15 | 8 | 4 |
2 | 40 | 30 | 25 | 8 | 7 |
To calculate the final price of a sale I created a calcualted column with the following formula:
Final_Price = Unity_Price + Tax_1 + Tex_2 - Tax_3 - Tax_4
Now I want to filter the final price by my columns names:
Slicer --
Unity_Price | Tax_1 | Tax_2 | Tax_3 | Tax_4 |
Table Visual --
Sale_ID | Final_Price |
1 | 18 |
2 | 80 |
If I click on Unity_Price the it must shows this:
Slicer --
Unity_Price | Tax_1 | Tax_2 | Tax_3 | Tax_4 |
Sale_ID | Final_Price |
1 | 10 |
2 | 40 |
Or if I multiselect items, all taxes for example, it must shows this:
Slicer --
Unity_Price | Tax_1 | Tax_2 | Tax_3 | Tax_4 |
Sale_ID | Final_Price |
1 | 8 |
2 | 40 |
How can I do that?
Thanks.
Solved! Go to Solution.
Hi @Anonymous,
I would solve this using a measure do the following:
Final_Price = VAR Unity_price = IF ( CONTAINS ( Slicer, Slicer[Selection], "Unity_Price" ) = TRUE (), SUM ( Sales[Unity_Price] ), 0 ) VAR Tax1 = IF ( CONTAINS ( Slicer, Slicer[Selection], "Tax_1" ) = TRUE (), SUM ( Sales[Tax_1] ), 0 ) VAR Tax2 = IF ( CONTAINS ( Slicer, Slicer[Selection], "Tax_2" ) = TRUE (), SUM ( Sales[Tax_2] ), 0 ) VAR Tax3 = IF ( CONTAINS ( Slicer, Slicer[Selection], "Tax_3" ) = TRUE (), SUM ( Sales[Tax_3] ), 0 ) VAR Tax4 = IF ( CONTAINS ( Slicer, Slicer[Selection], "Tax_4" ) = TRUE (), SUM ( Sales[Tax_4] ), 0 ) RETURN Unity_price + Tax1 + Tax2 - Tax3 - Tax4
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous,
You can do this making of 3 options
Using this post I adapted the situation and made the following
Country Job Hierarchy = UNION ( SELECTCOLUMNS ( 'Hierarchy'; "Badge ID"; 'Hierarchy'[BadgeID]; "Hierarchy Name"; 'Hierarchy'[Job Title]; "Level"; "Job Title"; "LevelNumber"; 1 ); SELECTCOLUMNS ( 'Hierarchy'; "Badge ID"; 'Hierarchy'[BadgeID]; "Hierarchy Name"; 'Hierarchy'[Country]; "Level"; "Country"; "LevelNumber"; 2 ) )
j
2. Unpivot Column (link to unpivot columns)
3. Bookmarks (link to bookmarks documentation)
See below the screenshot and the PBIX file with all the options above.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous,
Don't know how I could help with this post but glad it serves your purposes.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
I am trying to achieve something similar and I really like the Dynamic hierarchies option and is also suitable for my requirement. I have created a new table using the DAX query and joined with the main table, the totals in the two result sets is same but the breakdown is not right. I am not sure why this is happening, please tell me if I am missing anything.
Thanks in advance
HI @Anonymous ,
Without any specification and data about your model is difficul to give you an answrr, but have you checked if you hierarchy model is linked in a many to many relationship and with cross filtering active?
Can you share your file? If information is sensitive do a mockup file or shared it trough private message.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHI @MFelix, Thats superb!,
I liked you have posted 3 methods and i found Unpivot is easy for me since I am not expert in Dax code. I have used your method in my table using Unpivot and everthing looks great except i have one more requirment which i forgot to mention in previous post.
1) I want to show the Top 5 values in the chart, I tried to add Visual level filter but nothing happened.
2) I saw that Donut Chart is showing value for Both Attribute in single chart but I want to show either Country or JobTitle only at the same time. Can we select one value by default or is there any other method to achive the same? In the real data I have 7 attributes that need to be shown in one donut chart.
Please see the attached screenshot:
I found that Bookmark mehtod is good if we don't have any other slicer in the chart but when we use multiple slicer that change the chart Bookmark method is failed.
Thank you so much for the detail answer.
Kulchandra
Hey Meflix,
I've almost similar issue but I'm not able to resolve it the way you mentioned.
Following are the steps
I tried following the code you mentioned but instead of Sum(Sales[VAR]) I want Divide(VAR, Column Name)
The code is returning a lot of errors
ETA_Quality =
VAR On_Time =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "On Time" ) = TRUE (),
Divide( 'Volvo ETA'[On-Time], 'Volvo ETA'[Total], Blank()),
0
)
VAR Early =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "Early" ) = TRUE (),
Divide( 'Volvo ETA'[Early], 'Volvo ETA'[Total], Blank()),
0
)
VAR Late =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "Late" ) = TRUE (),
Divide( 'Volvo ETA'[Late], 'Volvo ETA'[Total], Blank()),
0
)
VAR Cancelled =
IF (
CONTAINS ( Slicer, Slicer[V_Selection], "Cancelled" ) = TRUE (),
CALCULATE( 'Volvo ETA'[Cancelled] 'Volvo ETA'[Total], Blank()),
0
) Return Cancelled+Early+Late+On_Time
Also instead of last Return Cancelled+Early+Late+On_Time, I just want to Return all of them which will eventually display the VAR value based on Column Selection.
Thanks!
Hi @Anonymous,
When you use measure they are calculated based on context so you cannot make the calculations based on a single value from a colum, meaning that when you have
VAR On_Time = IF ( CONTAINS ( Slicer, Slicer[V_Selection], "On Time" ) = TRUE (), Divide( 'Volvo ETA'[On-Time], 'Volvo ETA'[Total], Blank()), 0 )
You should have something like this:
VAR On_Time = IF ( CONTAINS ( Slicer, Slicer[V_Selection], "On Time" ) = TRUE (), DIVIDE ( SUM ( 'Volvo ETA'[On-Time] ), SUM ( 'Volvo ETA'[Total] ), BLANK () ), 0 )
In this way you are aggregating the values, however be carefull because this can sometimes based on context not return the expected result.
Can you please share some sample data and show the expected result in that way I can adjust the formula better.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi MFelix,
I want to create a slicer of multiple columns based on another slicer having parameters.
for example raw data is like this:
Event | Student ID | Maths teacher | Science Teacher | English Teacher |
Exhibition | 1 | Ravi Chandra | Spriha Das | Vandana p |
Exhibition | 2 | RK Das | Mamta Chobey | |
Exhibition | 3 | Rajesh Ranjan | Rajesh Ranjan |
So first slicer should have values like Maths, Science and English:
Slicer 1 |
Maths |
Science |
English |
And second slicer should filter based on Slicer 1 for example if Maths is selected in slicer 1 , then slicer 2 should have following values:
Slicer 2 |
Ravi Chandra |
RK Das |
And table should show this:
Event | Student ID | Maths teacher |
Exhibition | 1 | Ravi Chandra |
Exhibition | 2 | RK Das |
I tried with field parameters but it cant be used in report server
Please help me with this.
Hey @MFelix ,
Thanks for the reply. I realised the issue now and almost fixed it. Also what are you tryiig to say in the first step? I created a different table with all the four columns in it named Early, Late, Cancelled, On Time but I cannot still add the slicer which gives me all column names in the selection. Can you explain the first step a little more?
It's giving me this error:
Failed to resolve name 'Slicer'. It is not a valid table, variable, or function name.
Thanks!
Niket
Hi @Anonymous,
In the previous post the name of the table was Slicer that's why it's in the formula.
You need to place the name of the table you used to make the slicer selection.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |