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! ). 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.
I would appreciate if you could guide me into a right direction on how to solve this problem.
Thank you in advance
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 :
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.
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
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?
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?
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.
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:
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.
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.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!