Measures in Table Visual

[sheap069]

Hi,

I have two data sets as shown here (sample data and expected result)

data 1

Application ID	Attribute 1	Attribute 2	Attribute 3	Attribute 4
1	TRUE	FALSE	FALSE	TRUE
2	FALSE	FALSE	FALSE	TRUE
3	TRUE	FALSE	FALSE	TRUE
4	FALSE	TRUE	FALSE	TRUE
5	FALSE	FALSE	FALSE	TRUE
6	FALSE	TRUE	FALSE	TRUE

data 2

Application ID	Attribute 1	Attribute 2	Attribute 3	Attribute 4
7	FALSE	FALSE	TRUE	FALSE
8	TRUE	FALSE	TRUE	FALSE
9	TRUE	TRUE	TRUE	FALSE
10	FALSE	FALSE	TRUE	FALSE
11	FALSE	FALSE	TRUE	FALSE
12	TRUE	TRUE	TRUE	FALSE

I have many measures to calculate counts and rates of the attributes. Those are calculated based on if the Attributes are true or false. These are the results 

data 1

	Attribute 1	Attribute 2	Attribute 3	Attribute 4
Count	2	2	0	6
Rate	33%	33%	0%	100%

data 2

	Attribute 1	Attribute 2	Attribute 3	Attribute 4
Count	3	2	6	0
Rate	50%	33%	100%	0%

I want to have these measures in a Table Visual like this: 

	Data 1	Data 2
	# of Apps	Rate	# of Apps	Rate
Attribute 1	2	33%	3	50%
Attribute 2	2	33%	2	33%
Attribute 3	0	0%	6	100%
Attribute 4	6	100%	0	0%

I'm not sure how to construct this table based on all the measures I've created in my data. 

Thank you

[v-easonf-msft · Accepted solution]

Hi,  @sheap069 

Try follow steps :
1.Add a custom column to mark table name in each table

2. Upivote attribute columns  as below:

3.Append queries as new query and then close & Apply

You will get a new table as below:
(make sure the field "Value" is text type)

4.Create measure as below：

Count_value = 
CALCULATE (
    COUNT  ( Append1[Value] ),
    FILTER (
        ALLEXCEPT ( Append1, Append1[Table Name], Append1[Attribute] ),
        Append1[Value] <> BLANK ()
    )
) + 0

Count_value_true = 
CALCULATE (
    COUNT( Append1[Value] ),
    FILTER (
        ALLEXCEPT ( Append1, Append1[Table Name], Append1[Attribute] ),
        Append1[Value] = "True"
    )
) + 0

Rate = [Count_value_true]/[Count_value]

The  result will show as below:

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sheap069]

Thank you very much, this has the desired result!

[Fowmy]

@sheap069 

The best approach to get the desired results is to UnPivot the Attribute columns do the calculation using DAX.

[sheap069]

Hi,

Could you provide an example please? How would I do the calculations? Do I do it in the Power Query Editor in the table?
Thank you 

[Fowmy]

@sheap069 

You can do the transformations in PQ and the calculations using DAX after loading the data from PQ.

To unpivot data, refer to this video: 

https://youtu.be/Vff2kRBM95o