Sort Matrix and Chart with KPI Values

[ramiouanes]

Hello,

I have a SSAS tabular model with a Time Analysis dimension and a KPI dimension.

The KPI Dimension contains members with values of type: Sales, Budget...etc.

The Time Analysis Dimension contains members of type: Periodic, YTDN, YtDN-1...etc.

I have one measure in the model with an IF statement based on both KPI and Time Analysis dimensions. So for example to calculate Periodic Sales, I have an expression containing " IF( KPI[Name]="Sales" && TimeAnalysis[Name]="Periodic"; calculate(sum(Fact[valueColumn]); Filter( Fact[KPIColumn]="Sales"));Blank()); "

What I want to achieve eventually is a sort on the matrix column based on Sales Amount. So the table would be something like this: 

Rows: Month

Columns: KPI/TimeAnalysis

Values: Value

	Sales	Budget
Month	Periodic	YTDN	YTDN-1	Periodic	YTDN	YTDN-1
January	10	10	5	10	10	5
February	3	13	4	5	15	6
March	400	413	45	390	405	45

And I want to be able to sort the matrix with the values from the Sales/YTDN Column to obtain something like this:

Sort by column in Red:

	Sales	Budget
Month	Periodic	YTDN	YTDN-1	Periodic	YTDN	YTDN-1
January	400	413	45	390	405	45
February	10	10	5	10	10	5
March	3	13	4	5	15	6

Same thing for a chart, where I have Month in axis, Time Analysis in Legend and Value in values and I want to sort the chart by Sales/YTDN value. 

Is there a way to achieve this? Thanks in advance for any input.

[v-yuta-msft]

Hi ramiouanes,

" IF( KPI[Name]="Sales" && TimeAnalysis[Name]="Periodic"; calculate(sum(Fact[valueColumn]); Filter( Fact[KPIColumn]="Sales"));Blank()); "

<--- Does this mean you want to create two slicer charts based on KPI[Name] column and TimeAnalysis[Name] column? And if user select "Sales" and "Periodic", return value of calculate(sum(Fact[valueColumn]); Filter( Fact[KPIColumn]="Sales"))? If so, you should modify your measure like pattern below:

Measure =
IF (
    SELECTEDVALUE ( KPI[Name] ) = "Sales"
        && SELECTEDVALUE ( TimeAnalysis[Name] ) = "Periodic",
    CALCULATE (
        SUM ( Fact[valueColumn] ),
        FILTER ( Fact, Fact[KPIColumn] = "Sales" )
    ),
    BLANK ()
)

In addtion, to sort values in a column, just right click on the column then click "Sort".

Regards,

Jimmy Tao

[ramiouanes]

Hello @v-yuta-msft,

Actually, I'm not trying to put a slicer for the measure.

It's a matrix that I'm using with KPI and Time analysis as columns and a column Value (That contains the sum of all my KPIs) as values.

The sort does not work though. The idea is to be able to sort only one KPI. And the KPI here are grouped in one column which is KPI[Name]. So the right click/Sort doesn't work.