Dynamic Commentary based from column and slicer

[Kalachuchi]

I have two tables which are connected via unique ID

UniqueID	Year	Name	Sales
1	2019	Arthur Morgan	2
2	2020	James Morgan	45

Slicer

UniqueID	Region
1	OC
2	ASEAN

So when the user has clicked OC in slicer the commentary will be "In 2019, Arthur Morgan from OC region has a sale count of 2"

then if ASEAN is clicked "In 2020, James Morgan from ASEAN region has a sale count of 45"

I have no idea if this is possible or not, Any help will greatly be appreciated.

[Anonymous]

Hi  @Kalachuchi  ,

Here are the steps you can follow：

 1. Create measure.

Measure =
var _select =SELECTEDVALUE('Table (2)'[Region])
var _OC=CALCULATE(MAX('Table (2)'[Region]),FILTER('Table (2)',[UniqueID]=MAX('Table'[UniqueID])))
var _amount=SUM('Table'[Sales])
return
IF(_select=MAX('Table (2)'[Region]),
"In "&MAX('Table'[Year])&", "&MAX('Table'[Name])&" from "&_OC&" region has a sale count of "&MAX('Table'[Sales]),"The total amount of sales in two years is"&" "&_amount&"")

 2. Result.

You can downloaded PBIX file from here.

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.

[Anonymous · Accepted solution]

Hi  @Kalachuchi  ,

Here are the steps you can follow：

1. Create calculated column.

Comment =
var _OC=CALCULATE(MAX('Table (2)'[Region]),FILTER('Table (2)',[UniqueID]=EARLIER('Table'[UniqueID])))
return
"In "&[Year]&", "&[Name]&" from "&_OC&" region has a sale count of "&[Sales]

2. Result

You can downloaded PBIX file from here.

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.

[Kalachuchi]

Is it possible to add if the user did not clicked any slicer it will give me a static text with the total number of sales?