Forum Discussion

Earl40's avatar
Earl40
Helper I
2 years ago

Need help creating a table in PowerBi

Hello,

 

I currently have a PowerPoint table thats manually updated and need to create it in PowerBi.  The goal is to create a table that shows the % of items aged >30 days for the notification type in the last 3 months (it 99% of the time going to be 0%). The end result should look something like this 

 

I started by first trying to create the first part (was gonna add in the mon ths part later) which is determine %. I tried to create a measure that counts the # of notification items aged > 30 days  divided by the total # of notification. My formula isnt working and unsure why (screen shot below) . The next step would then be figuring out the month filter. I have attached 

 

 

 

I posted the sample report and dataset on drop box

 

Data Excel- https://www.dropbox.com/scl/fi/d8fj5553qaxtyhxmxl6m2/Data-Example.xlsx?rlkey=nsm8xvwbv3u1umkk48ho2calh&dl=0

PowerBi - https://www.dropbox.com/scl/fi/o8fpb6flmci8xddlfh7ig/Sample.pbix?rlkey=uhnwsilk0ty4mtodfgghssi1p&dl=0

 

Thank you in advance 

 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  Earl40 ,

     

    Here are the steps you can follow：

    1. Create calculated table.

    Main_Table =
var _table1=
CALENDAR(
    DATE(2023,1,1),DATE(2023,12,31))
var _table2=
ADDCOLUMNS(
    _table1,"Year",YEAR([Date]),"Month",FORMAT([Date],"mmmm"))
var _table3=
{"Notification closed aged > 30 Days"}
var _table4=
SUMMARIZE(_table2,[Year],[Month])
return
CROSSJOIN(
    _table4,_table3)

    2. Create measure.

    Test =
   CALCULATE(
       DISTINCTCOUNT('Data Table'[Item #]),
        FILTER(ALL('Data Table'),
        YEAR('Data Table'[Create Date])=MAX('Main_Table'[Year])&&FORMAT('Data Table'[Create Date],"mmmm")=MAX('Main_Table'[Month])&&'Data Table'[Age]>=30&&'Data Table'[Type]="Notification"))

    3. Placement in Matrix Visual.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

    • Earl40's avatar
      Earl40
      Helper I
      2 years ago

      Anonymous  Thanks, quick question which column field from the table should be added. I highlighted the section in the screen shot below.

       

      Thank You

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  Earl40 ,

     

    ADDCOLUMNS(
    _table1,"Year",YEAR([Date]),"Month",FORMAT([Date],"mmmm"))

    ADDCOLUMNS function means:

    Adds calculated columns to the given table or table expression.

    ADDCOLUMNS function (DAX) - DAX | Microsoft Learn

     

    The function _Table1 creates a table of dates in the interval 2023.1.1 - 2023.12.31.

    "Year",YEAR([Date]): represents the creation of a column with the name Year, and the data inside is the Year of each date.

    "Month",FORMAT([Date], "mmmm")): represents the creation of a column, the column name is Month, the data inside is the month of each date, the month is in the format of "mmmm".

    Format () function means:

    Converts a value to text according to the specified format.

    FORMAT function (DAX) - DAX | Microsoft Learn

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.