Get members and values based on rank from Datamodel

Hi

In short, I do have a datamodel, I need to get the TOP 1 member of one measure and populate the name and values of one dimension (reasons)
I need to do this 2 times, one time for Downtime, one time for scrap

Background:
3 Value tables
1 Downtime table, 1 Scrap table, 1 Index table, each with date, machine and articel

3 Dimension table
1 Date, 1 machine, 1 articel

All of them are connected in the datamodel area

Now I do want to create a dynamic dashboard where I can choose a month (or period) and then see the following
Top 3 for one measure of all machines (Measure Avg Index in the Index table)
For the Top 1 of the period i want to get a chart for the downtime reasons
Same for Top 2

I can do this with pivot charts but if the month change and also the top 3, i need to manually change and select the machines to catch the top 1

So it is not really dynamic. I think this should work with the cube formulas?

I can use the Top3 Pivottable as basis, then take the number 1 and create a Preparation table for my chart. This preparation table is showing the measure Downtime(h) and populate the reasons for the selection (Month and Machine) and their values. This can be then used to create my chart.


I prepared an example table, the sheet with the pivot table is basically my results, just manual choose every time I choose a different period.

I am able to use the cubevalue formuals already, for other cube formula I am not yet fit.

Every help is appreciated

thanks

Hi,

I haven't looked at your CUBE formula approach yet, but I would do it in Power Pivot, viz:

Create a new Measure:

Top Avg Index :=
VAR MyTable =
SUMMARIZE ( Index, Index[Machine], "Average Index", AVERAGE ( Index[Index] ) )
RETURN
MAXX ( MyTable, [Average Index] )

Then filter your PvtTop1, used to generate the chart, via Value Filters/Top 10 and 'Top 1 Item by Top Avg Index'.

Edit: this isn't in fact necessary, you could simply apply the same filter as above but using your Avg Index measure, rather than my Top Avg Index one.

Regards

Hi

I tried the filter Topx, did not know I can filter by a measure which is not in the table itself. Was helpfull, thanks
In the PvtTop1 I can now show the top3 of the selected month or period. As I do want to create a chart sorted by the reasons for each Top1 to Top3, I thought to add a rank to the index, then use 3 pvt tables and make the chart.
My formula is

Please Login or Register  to view this content.

but I do get wrong results of the Rank, always a 1.

Any ideas why the rankx formula only spill out 1
I guess for my final dashboard I most likley need to combine the pivot table and some cube formulas.


For my cubeformulas I do have two big issues.

1. Nonempty values in the cubeset

Please Login or Register  to view this content.

I still get reasons in the ranked member although the measure do not have values
ranked member cubevalue
Idea wrong 17.5
High Power 13.9
No Material 3.6
(blank)
No Man

2. Combining slicers in cubeset
I am able to combine multiple slicers with this approach.
First get the first value of the slicer
D6:

Please Login or Register  to view this content.

Then in the cubeset I just link to this cell but need to define all the way down ([Dim_Date].[Month].["&$D$6&"],)

Please Login or Register  to view this content.

Is there a way to attack these 2 issues?


thanks

Hi,

As far as I was aware, my previous post had solved your original requirement. If you have new requirements, perhaps you could re-upload your workbook with the expected results clearly outlined?

Regards

First, you have a blank row at the end of your DownTime table which I suggest you remove.

Second, your rank measure should be:

RankIndex:=RANKX(ALL(Dim_MachineList),[Avg Index],,ASC)

After which you can filter your pivots by setting this value as desired, e.g. to 1, 2, etc.

Regards