Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

table creation

Hi ,

    i need to create a table from following data...Add a slicer for underwriter and table will show details of calcualtion like below screen shot..

GWP= sum of POLICY PREMIUM WITHOUT EQ, TRIA, TERRORISM, OR SURCHARGE for the year 2022

Budget=sum of amount for the year 2022

Actual2021=sum of Surcharge for the year 2021

..Total 4 column will be there in table visual and one slicer will be tehre with underwriter values...

after selecting slicer value need to changed based on each underwriter...

 

 

https://www.dropbox.com/s/4hex754x11a8pq6/fact.pbix?dl=0 .

plse help?

Need Help
Show and Tell
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, I download your sample, here's my solution.

    1.Create two calculated columns in fact table.

    Month_year = FORMAT('fact'[invoice_date],"MMM"&"/"&YEAR('fact'[invoice_date]))
    Year = YEAR('fact'[invoice_date])

    Create a calculated column in budget table.

    Month_year = FORMAT(DATE(1,'budget'[month],1),"MMM")&"/"&'budget'[year]

    2.Make relationship between the two tables with the Month_year column.

    3.Select Month_year column from fact table and other columns you want to sum into the table visual, make two slicers, it will get the correct sum result according to the underwriter and year.

    I attach the sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

     

2 Replies

  • jaipal's avatar
    jaipal
    Resolver III
    4 years ago

    Anonymous  Hi, you should need unique identifier (Primary key) to make relationship between both tables, then it can be done

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    According to your description, I download your sample, here's my solution.

    1.Create two calculated columns in fact table.

    Month_year = FORMAT('fact'[invoice_date],"MMM"&"/"&YEAR('fact'[invoice_date]))
    Year = YEAR('fact'[invoice_date])

    Create a calculated column in budget table.

    Month_year = FORMAT(DATE(1,'budget'[month],1),"MMM")&"/"&'budget'[year]

    2.Make relationship between the two tables with the Month_year column.

    3.Select Month_year column from fact table and other columns you want to sum into the table visual, make two slicers, it will get the correct sum result according to the underwriter and year.

    I attach the sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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