Forum Discussion
Create a slicer from a measure
Hi,
I need help creating a slicer based on a measure.
Scenario
I have 3 tables, one with sales and another with costings both linked with the stockmaster tabel through item codes, i have a measure between the 2 that calculates the GP%.
Now i want to create a 3 button visual which will allow me to filter the data into 3 ranges
2% and low, 2%-5% and 5% and higher.
When i click on 2% and lower it will show me the sales with GP% of 2% and lower
It seems like you want to create a calculated column for GP% (Gross Profit Percentage) and then create a slicer based on the range of GP%. However, Power BI doesn't allow a slicer directly from a measure.
1. Join the 3 tables based on Item Code.
2. Create a new calculated column for GP%, the formula should be something like: `(Sales[Unit Price] - LAST COSTS[Last PO Cost]) / Sales[Unit Price]`
3. Create a new column "GP% Range", which will be used to determine the range of GP%.
Here are the DAX formulas:
1. Join tables:
If they aren't joined already, you can create relationships between these tables in Power BI based on Item Code. This isn't done with DAX, but in the modeling section of Power BI Desktop.
2. GP%:
```DAX
GP% = (Sales[Unit Price] - 'LAST COSTS'[Last PO Cost]) / Sales[Unit Price]
```3. GP% Range:
```DAX
GP% Range =
SWITCH (
TRUE(),
'Sales'[GP%] <= 0.02, "2% and lower",
'Sales'[GP%] > 0.02 && 'Sales'[GP%] <= 0.05, "2%-5%",
'Sales'[GP%] > 0.05, "5% and higher",
"Other"
)
```This new "GP% Range" column can be used to create the slicer, and this will filter your data according to the range of GP% when you select different buttons in the slicer.
Please be aware that creating calculated columns, especially those using SWITCH or other similar functions, could have a performance impact on your Power BI report, especially if you have a large volume of data. As always, make sure to test the performance and adjust as necessary.
Based on the data you provided the slicer contains only the current value :
I am attaching the PBIX file.
5 Replies
I am supposing that your measure is like below :
GP_Percentage = DIVIDE(SUM('Sales'[SalesAmount]) - SUM('Costings'[CostAmount]), SUM('Sales'[SalesAmount]))In the formula bar, create a table with three rows representing the three ranges: "2% and low," "2%-5%," and "5% and higher." You can use the following DAX formula to create the table:
FilterTable =
DATATABLE (
"Range", STRING,
{
{"2% and low"},
{"2%-5%"},
{"5% and higher"}
}
)Drag and drop the "Range" column from the "FilterTable" you created in Step 2 onto your report canvas.
Next, create a measure that will filter the data based on the selected range. For example, let's call this measure:
SelectedRangeFilter = VAR SelectedRange = SELECTEDVALUE('FilterTable'[Range]) RETURN SWITCH ( SelectedRange, "2% and low", CALCULATE(SUM('Sales'[SalesAmount]), 'Sales'[GP_Percentage] <= 0.02), "2%-5%", CALCULATE(SUM('Sales'[SalesAmount]), 'Sales'[GP_Percentage] > 0.02 && 'Sales'[GP_Percentage] <= 0.05), "5% and higher", CALCULATE(SUM('Sales'[SalesAmount]), 'Sales'[GP_Percentage] > 0.05), BLANK() )This measure uses the SWITCH function to check the selected range and applies the corresponding filter based on the GP% values in the 'Sales' table.
Ok so done step one but getting a place holder error with the measure, please advise (Note i did modify with my table names)
It seems like you want to create a calculated column for GP% (Gross Profit Percentage) and then create a slicer based on the range of GP%. However, Power BI doesn't allow a slicer directly from a measure.
1. Join the 3 tables based on Item Code.
2. Create a new calculated column for GP%, the formula should be something like: `(Sales[Unit Price] - LAST COSTS[Last PO Cost]) / Sales[Unit Price]`
3. Create a new column "GP% Range", which will be used to determine the range of GP%.
Here are the DAX formulas:
1. Join tables:
If they aren't joined already, you can create relationships between these tables in Power BI based on Item Code. This isn't done with DAX, but in the modeling section of Power BI Desktop.
2. GP%:
```DAX
GP% = (Sales[Unit Price] - 'LAST COSTS'[Last PO Cost]) / Sales[Unit Price]
```3. GP% Range:
```DAX
GP% Range =
SWITCH (
TRUE(),
'Sales'[GP%] <= 0.02, "2% and lower",
'Sales'[GP%] > 0.02 && 'Sales'[GP%] <= 0.05, "2%-5%",
'Sales'[GP%] > 0.05, "5% and higher",
"Other"
)
```This new "GP% Range" column can be used to create the slicer, and this will filter your data according to the range of GP% when you select different buttons in the slicer.
Please be aware that creating calculated columns, especially those using SWITCH or other similar functions, could have a performance impact on your Power BI report, especially if you have a large volume of data. As always, make sure to test the performance and adjust as necessary.
Based on the data you provided the slicer contains only the current value :
I am attaching the PBIX file.
Hey TaariqHussain
You have to create 3 measures for 2% and low, 2%-5% and 5% and Higher,
After that, you have to create a field parameter for using 3 newly created measures.
You can refer below video
https://www.youtube.com/watch?v=-nqEv2YXLsU&t=444s&ab_channel=HowtoPowerBI
If this helped you, please mark my solution as accepted so that others can find it quickly while facing similar issues. Thank You!
