Calculated table working with slicers // Ordering matrix with different measures

[lorenzoo]

Hi there everyone, 

I'm a beginner and I'm building my first dashboard. I'm analyzing questionnaire based data, stored on an excel file in this format: 

Respondent ID		Country		Social Media A		Social media B		Social media C		Total
1		Country A
2		Country B				Social media B				1
3		Country C		Social Media A		Social media B		Social media C		1
4		Country B		Social Media A						1
5		Country A						Social media C		1

I'm trying to build a visualization that shows how many people use a given social media, which slices based on nation. What I tried was at first to create a column equal to null if every Social media cell was empty, as per Respondent1, and equal to 1 if at least one social media cell showed a value. Then, i created three new different measures as follows:
Social media x measure= count [Social media x] / count [Total].
This allowed me to create a matrix where to put all the measures as values, that slice correctly but which I cannot order from highest to lowest: 

Social media A		X%
Social media B		Y%
Social media C		Z%

Alternatively I tried to create a calculated table where each row was written in DAX as follows: 
Table= {("social media A", Social media A measure), ("social media B", Social media B measure), ("social media C", Social media C measure)}. 
I now have another table which I can order at wish but does not update according to the nation slicer. How can I solve this problem and ultimately obtain a table that I can order and updates with slicers?

[Sahir_Maharaj · Accepted solution]

Hello @lorenzoo,

Can you please try this:

1. Create a measure to calculate the percentage of responses for each platform:

Social Media Usage = 
DIVIDE(
    COUNTROWS('UnpivotedData'), 
    CALCULATE(COUNTROWS('UnpivotedData'), ALL('UnpivotedData'[Attribute]))
)

2. Create a rank measure based on the social media usage

Rank Social Media = 
RANKX(
    ALL('UnpivotedData'[Attribute]), 
    [Social Media Usage], 
    , DESC, Dense
)

Hope this helps!

[Anonymous · Accepted solution]

Hi @lorenzoo ,

@Sahir_Maharaj  provides a good solution. I still have some ideas here, I hope it will be helpful to you.

1. Enter Power Query Editor, hold down the Ctrl key to select the three columns as shown in the figure below, and select Unpivot Columns.

The obtained data is shown in the figure below.

2. Create Measure and write expressions. If the data that wants to be empty is not involved in calculation, you can add more conditions to the second parameter of the Filter function.

Measure =
Var _Cou1 = Calculete (Countries ('Table'), Filter (all ('table'), 'table' [value] <> black () && 'table' [value] = max ('table' [value]) && && 'Table' [country] = max ('table' [country])))
Var _Cou2 = Calculete (Countries ('Table'), Filter (all ('table'), 'table' [country] = max ('table' [country])))
Return
Divide (_Cou1, _Cou2)

3. If you want to sort the results, you can click the small triangle under the list to be raised or sorted.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[lorenzoo]

Many thanks! That's the solution I was looking for 🙂 

[Sahir_Maharaj · Accepted solution]

Hello @lorenzoo,

Can you please try this:

1. Create a measure to calculate the percentage of responses for each platform:

Social Media Usage = 
DIVIDE(
    COUNTROWS('UnpivotedData'), 
    CALCULATE(COUNTROWS('UnpivotedData'), ALL('UnpivotedData'[Attribute]))
)

2. Create a rank measure based on the social media usage

Rank Social Media = 
RANKX(
    ALL('UnpivotedData'[Attribute]), 
    [Social Media Usage], 
    , DESC, Dense
)

Hope this helps!

[lorenzoo]

Many thanks! 😀