table creation

[Anonymous]

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?

[v-yanjiang-msft · Accepted solution]

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.

[jaipal]

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