Order a slicer based in a measure

[FJME]

Dear colleagues!

In a auto production industry dashboard i created a new measure (DefeitosResumo) to count the defects (TotalDefects) by type (Defects), based in the column Defects from FIS table. After it, i created a slicer with the type of defects (Defeitos) but i can't order it by quantity of defects, although i ordered previously (in TotalDefeitos column measure). I think that is not possible to order it as derive from a measure. Can you help with some workaround ?

Best regards

Francisco

[FJME · Accepted solution]

The only problem is that it lacks the axis ordenation in the slicer . My apology and thanks for all the efforts. Best regards. FE

[FJME]

Hi bhanu!

I finnaly was able to create the functions recommended. However when i created the slicer, it simply order the defects alphabetically and i can´t order it (descendently) by the total of defects. Best regards. FE

[Anonymous]

Hi,

Thanks for the solution @bhanu_gautam  provided, and i want to offer some more information for user to refer to.

hello @FJME , after you creating the defectcounts table, you can click the defect column, then select sort by TotalDefects column, you can refer to the following picture.

Then sort the totaldefect column ascending/descending, then put the defect field to the slicer, it can work.

If the solutions @bhanu_gautam  and i offered help you solve the problem, please consider to mark them as solutions.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FJME · Accepted solution]

The only problem is that it lacks the axis ordenation in the slicer . My apology and thanks for all the efforts. Best regards. FE

[bhanu_gautam · Accepted solution]

Can you accept our reply as solution if it helps

[FJME]

Good morning!

The situation remains. I tried one more time follow the instructions (please see next image)

but when i create the slicer in report view, he turns to alphabetical order as you can see in picture below...

Best regards

FE

[FJME]

Bhanu !

Unfortunately i can't surpass the sort order step (after created the table). How can i send you the .pbi ? Best regards. Thanks. FE

[bhanu_gautam]

@FJME , First create a calculated table that includes defect types

DefectCounts =
SUMMARIZE(
FIS,
FIS[Defects],
"TotalDefects", COUNT(FIS[Defects])
)

Then create a measure for Sorting

SortOrder =
RANKX(
ALL(DefectCounts),
[TotalDefects],
,
DESC,
DENSE
)

Select the DefectCounts table in the Fields pane.
Click on the Defects column.
Go to the Column tools tab and click on Sort by Column.
Select the SortOrder measure.

Create the Slicer
Create a slicer using the DefectCounts table.

Go to the Report view.

Add a slicer visual to the report.
Drag the Defects column from the DefectCounts table to the slicer.
Use the Slicer to Filter the Data
Use the slicer to filter the data in your report.

Add the necessary visuals to your report.
Use the slicer to filter the data based on the defect types.

[FJME]

Hi Bhanu! Thanks by your rapid answer. However PBI informs is not possible determine the value of TotalDefects as the colunm don't exists or don't exists no actual line for that column. Best regards

[bhanu_gautam]

@FJME , first use this and create new calculated column 

DefectCounts =

SUMMARIZE(

FIS,

FIS[Defects],

"TotalDefects", COUNT(FIS[Defects])

)

This is creating new column totaldefects using summarise function 

[FJME]

..but ate not the some instructions used above to create the "calculated table"?

[bhanu_gautam]

Yes it is I think measure is not correctly defined, create below measure

     TotalDefects = COUNT(FIS[Defects])

Then calculated column 

DefectCounts =
SUMMARIZE(
FIS,
FIS[Defects],
"TotalDefects", [TotalDefects]
)

Then a measure for sorting

SortOrder =
RANKX(
ALL(DefectCounts),
[TotalDefects],
,
DESC,
DENSE
)

Try this approach if you still face some issue share sample data