Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |