Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Get measures in data table

So I have a number of measures calculated from source data in different tables. All of these tables contain date fields, which are all linked to a central Calendar table.   I have made a matrix in ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-juanli-msft ,

 

Thanks for your reply. Unfortunately, this is not what I was asking for. I already have what I want in the matrix. I want some way of creating what I have outlined above as a table in the Data View, so I can then write measures, calculated columns that act on it there.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous 

You could create a new table

Table =
UNION (
    SELECTCOLUMNS (
        ADDCOLUMNS ( widget, "year/month", FORMAT ( [Date], "mmm-yy" ) ),
        "month-year", [year/month],
        "measure name", "Percentage of widget",
        "value", [Percentage of widget]
    ),
    SELECTCOLUMNS (
        ADDCOLUMNS ( staff, "year/month", FORMAT ( [Date], "mmm-yy" ) ),
        "month-year", [year/month],
        "measure name", "Staff attrition",
        "value", [Staff attrition]
    )
)

Best Regards
Maggie

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

     

    Hi v-juanli-msft ,

     

    Thanks. This is the kind of result I want, but I don't currently have tables with the measure results precalculated in them like the "widget" and "staff" tables that you have. Instead, I have tables with raw data, and measures that calculate from that raw data, as below:

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Measure formula is:

     

    Median elapsed working days to process any product (food, hygiene, etc.) licence or registration = MEDIAN('5 LRC_tblLicencesIssued'[Working Days])

    "Licence Issued Date" is linked to "'Calendar'[Date]" in my calendar table, and using the "'Calendar'[ShrtMnth]" field from that table I am able to generate the matrix:

     

     

    Given that my measures/tables are structured like this, is there a way to produce a result like you had in your post? Thanks again for your help so far.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

       

      Try this

       

      table Name = SUMMARIZE('Calendar','Calendar'[ShrtMnth],"Name of new column",Name of measure,"Name of new colum2,Name of new of measure2 etc
       
      Worked for me
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous ,

         

        Thanks for your help. The issue is that I need the names of the different measures to all be in the same column.