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
sylwestercz
Helper I
Helper I

Slicer with measures ?

Hello All,

 

I hope you can help a newbie. I recently started using PowerBI Desktop and I am in the process of building my first dashboard for the Revenues (excited! Smiley Very Happy). I will try to shortly explain my problem:

 

I have data table with all revenues (and categories, months, accounts, amounts etc ... as columns). I want to create a slicer which will filter all other visuals and their calculation based on account number, please see below explanation.

 

Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I would appreciate if you could guide me into a right direction on how to solve this problem.

 

Thank you in advance

Sly

10 REPLIES 10
CheenuSing
Community Champion
Community Champion

Hi,

 

You could take this approach.

 

Create a table called Measures. The contents will be 

MeasureName , MeasureID as column names

The rows will be

Gross Revenue , 1

Net Revenue , 2

 

Create a measure in the data model as follows :

 

Showvalue=switch (TRUE,
Min ( Measures[MeasureID] ) = 1,
( [SalesQty]  ),                            /* replace this by your fomula for Gross Revenue or if already exists put that measurename */
Min ( Measures[MeasureID] ) = 2,
( [Sales] ) )                              /* replace this by your fomula for Net Revenue or if already exists put that measurename */

 

 

Now create a slicer with Measure table column MeasureName .

 

In the chart or table , use the measure Showvalue as your values column to chart or display in table.

 

and you will have the results.

 

Best

 

Cheenusing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

hi Mr. Cheenusing,

 

i have an excel sheet with few columns like

Date, Name, State, Total Sales (numbers), M&S(Num), Elect(Num), Water(Num), Fuel(Num), etc.

 

i am not able to use slicer for M&S, Total Sales, Elec, Water, Fuel? how can i use this in the visualisation as slicer?

 

 

Many thanks for this excellent solution, really works so well - had seen this type of example before, but this works brilliantly..the penny has dropped!  

Thanks for posting this

Kind regards

Highlander

So good it deserved  2 posts.

(new to this community!_)

Really helpful, works brilliantly thank you so much!

I'll look out for your other solutions.

Excellent effort!

I have tried several variations of this approach with MeasureID being in one table and SalesQty being in an unrelated table (within a Tabular model).  The calculated column (measure) does not fail, but the slicer on MeasureID has no affect in Power BI.  Am I missing something?  Is it possible to slice a value from an unrelated table?

Hi,  

 

I am also having an issue with slicer pointing to calcualted measure. Is there a way to create a slicer in power bi that points to calculated measure? 

 

Thanks,

Gayatri

GeorgeR
New Member

Hi 

You can do the followings (without learning cube and other complex MDX functions):

1.) Add your source data to the model (import, link table, etc) 

2.) Insert a pivot table linked to the source data

3.) Add slicers connected to the pivot table above

4.) Arrange your pivot to show the desired aggregate values

5.) Convert your pivot to MDX functions 

6.) Delete the other (non summary) fileds

 

Use slicers to slice your aggregete values. 

 

I hope that helps. 

 

George

Hi @GeorgeR

Thank you for your answer however I do not fully understand all the steps.

 

I have also corrected the subject of this thread as I think I was not clear enough.

 

As I wrote I would like to have a slicer with Gross Revenue and Net Revenue:

4.jpg

 

 

 

 

 

and I would like that these items will be somehow linked to below created measures for both Gross and Net Revenues in order to filter all other visuals on the dashboard/report.

 

 

Gross Revenues = SUM('LEQ2 output'[Amount]) 

 

 

Net Revenues = CALCULATE(SUM('LEQ2 output'[Amount]),FILTER('LEQ2 output','LEQ2 output'[Account number]<>3207 && 'LEQ2 output'[Account number]<>3208))

 

I have browsed through the threads but did not find anything. Do you know if there is a workaround to accomplish this?

 

Thank you in advance.

Hi 

 

Your request is not very Power Pivot friendly. Means normally we work with calulated fields (such as Gross OR Net revenue) and slice and dice these to show the result.

You need to build Net trunover.=SUM(CALCULATE(..... and Gross turnover:=SUM(.....

 

Means change the display of net to gross or the other way around is done by removing a KPI and adding another to a Pivot (or create two Pivots, one for each).

This is what we tend to recommend. It will have significant advantages later. Think about further analyses, by filtering, slicing these values or using them to build further calculations. 

 

The second method is to introduce switch to your model. You create a named range (variable) on the Excel spreadsheet. You define a drop down list here (with ordinary non Power Pivot tools) and make the selection. So that the cell "Gross values" shows TRUE or FALSE.

Connect this cell as a linked table to your data model. Therefore you wil have switch of Gross / Net. Include this into the calulated fields: Displayed revenue:=IF( Gross is True, KPI1, KPI2) 

That way you will have a KPI of displayed revenue that is to display the Net or Gross revenue. Also you need to add an OnEvent macro to refresh lineked table on the change of drop down selection. (This requires that the user is able to run macros which is not the case every time.) Also you miss the slicer here.

 

Third approach is to add slicer

Power Pivot can slice values of a table only. So you need to add these fileds some ways into your model. This can give you a slicer to pick from on the worksheet. Then the other difficulty is to apply the slicer value onto the KPI. 

The slicer can slice values of a cube. So you need to duplicate the rows in your source data adding a row for Net and a row for Gross. The field of Gross/Net can be filtered by the slicer while the values can be sum-ed.

Again that needs heavy manipulation of data.  

 

In my first post I tried to suggest not to start complex MDX functions but use the PivotTable Tools> Analyze > OLAP Tools > Convert to formulas instead. Also you can refer to a slicer with 

=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Your KPI#X]",Slicer_Name1, Slicer_Name2, .....)

 

I hope that helps. 

 

Regards

George

 

 

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.