Forum Discussion
Slicer with measures ?
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:
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