Forum Discussion

sheap069's avatar
sheap069
Helper III
5 years ago
Solved

Measures in Table Visual

Hi,

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

 

data 1

Application IDAttribute 1Attribute 2Attribute 3Attribute 4
1TRUEFALSEFALSETRUE
2FALSEFALSEFALSETRUE
3TRUEFALSEFALSETRUE
4FALSETRUEFALSETRUE
5FALSEFALSEFALSETRUE
6FALSETRUEFALSETRUE

 

data 2

Application IDAttribute 1Attribute 2Attribute 3Attribute 4
7FALSEFALSETRUEFALSE
8TRUEFALSETRUEFALSE
9TRUETRUETRUEFALSE
10FALSEFALSETRUEFALSE
11FALSEFALSETRUEFALSE
12TRUETRUETRUEFALSE

 

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 1Attribute 2Attribute 3Attribute 4
Count2206
Rate33%33%0%100%

data 2

 Attribute 1Attribute 2Attribute 3Attribute 4
Count3260
Rate50%33%100%0%

 

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

 Data 1Data 2
 # of AppsRate# of Apps Rate
Attribute 1233%350%
Attribute 2233%233%
Attribute 300%6100%
Attribute 46100%00%

 

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 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.

5 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support

    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's avatar
      sheap069
      Helper III

      Thank you very much, this has the desired result!

  • sheap069 

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

    • sheap069's avatar
      sheap069
      Helper 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