Forum Discussion
Measures in Table Visual
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
Hi, sheap069
Try follow steps :
1.Add a custom column to mark table name in each table2. 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 () ) ) + 0Count_value_true = CALCULATE ( COUNT( Append1[Value] ), FILTER ( ALLEXCEPT ( Append1, Append1[Table Name], Append1[Attribute] ), Append1[Value] = "True" ) ) + 0Rate = [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.
5 Replies
- v-easonf-msftCommunity Support
Hi, sheap069
Try follow steps :
1.Add a custom column to mark table name in each table2. 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 () ) ) + 0Count_value_true = CALCULATE ( COUNT( Append1[Value] ), FILTER ( ALLEXCEPT ( Append1, Append1[Table Name], Append1[Attribute] ), Append1[Value] = "True" ) ) + 0Rate = [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.- sheap069Helper III
Thank you very much, this has the desired result!
- sheap069Helper III
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- FowmySuper User
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: