Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How to store measures in separate table

I have a table that looks like this:

 

CallIDAttempt(s)ColdTrf(s)WarmTrf(s)Consult(s)
1125522110
2199121692
396798069

 

I have measures that work out the standard deviations for each column (eg, attempt, cold, warm, consult)

 

I need to create a calclauted table that now stores the Standard deviation measures in a table like below:

 

SDAttempt(s)ColdTrf(s)WarmTrf(s)Consult(s)
159604855
211712592114

 

How can I do this within Power BI? 

 

The reason why I need it like this is that so I can use the SD column as a slicer.

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi Anonymous,

     

    If I understand your scenario correctly that you have created the measues that work out the standard deviations for each column (eg, attempt, cold, warm, consult) and you want to use the measures to create the a calculated table like the second table you post?

     

    If it is, what is the SD column? Normally, if you want to create a calculated table, you could use SUMMARIZE function and create the table like below.

     

    calculted table =
SUMMARIZE (
    'Tablename',
    "ID", 'TableName'[ID],
    "ColumnName1", [Measure1],
    "ColumnName2", [Measure2],
    "ColumnName3", [Measure3],
    "ColumnName4", [Measure4]
)

    If you still need help, please share your measure formulas and explain what's the logic of the SD column in the calculated table so that I could have a test and share the expression to you.

     

    Best Regards,

    Cherry

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-piga-msft

       

      Yes your solution does partly work, however as I have calculted the 2nd Standard deviation per column also, this will need to be added to the calculated table also.

       

      See below:

       

       

      Basically I need to add a new row underneath "SD1", as "SD2" with the associated SD2 measures. Looks like the Summarize function only allows you to add one row per column.

       

      Any ideas?

      • v-piga-msft's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        Hi Anonymous ,

         

        Sorry for the delay.

         

        Does first row of SD1 calculate the standard deviations for each column for the table in your first reply?

         

        What is the 2nd Standard deviation per column? Doest the 2nd Standard deviation reference the column in another table?

         

        If it is, I'm afraid that you should create another summarize table and then use UNION function to achieve your desired output.

         

        Table = UNION('Table2','Table3')

        In addition, you could write the measures in summarize table directly.

         

        Best  Regards,

        Cherry