Smart Narrative - show top 3 countries having highest sales among all other count

[bushanbo]

Using Smart Narrative, I need to show top 3 country names that have top sales of all the countries. If it's possible, please share the syntax.

ARRIA or Narrative Science, which are paid visuals will automatically generate this, but with Smart Narrative we can only write Custom stories as its giving very basic stories.

[bushanbo]

Thanks Kim for super quick response. 

I am using the logic you shared but giving 20th value in desc order and thats too repeating several times. 

I think we almost got it and minor change in logic will fix this.

File size is big but if necessary, I can remove all tables in model etc and share this PBIX file.

Table = PROBLEM

Metric = DISTINCTCOUNT(CSO_SID)

Column = Root Cause Service.

Top 3 Services = CONCATENATEX(TOPN(3, PROBLEM,DISTINCTCOUNT('CSO & JIRA Dataset'[CSO_SID]),DESC),PROBLEM[Root Cause Service], " , ")

[Jihwan_Kim]

Hi, @bushanbo 

Thank you for your feedback.

I am not sure whether I understood your measure correctly, but I assume you want to rank how many time each SID appear in the column.

Distinctcount is always 1, so I think it all rankings are rank NO. 1.

Please try something like below.

top 3 service = CONCATENATEX(

TOPN( 3,   VALUES(problem[SID]),    CALCULATE(COUNTROWS(problem)),   DESC   ),

problem[SID],    ", "   )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[bushanbo]

Thanks Kim.

I need to display under Smart Narrative as "Top 3 Services causing CSOs are Order Processing Services, Adobe.com, ExternalService - AWS"

Like I shared in snap in my previous reply, these 3 Services in blue have values as 60, 59, 59.

So I need a DAX which return single value separated by delimiter like Order Processing Services, Adobe.com, ExternalService - AWS with top 3 CSOs (this is metric).

Stories using the Smart Narrative as in snap below. Last line with these Top 3 is hard coded text but a DAX we need to make it dynamically to show top 3 names based on change in selected filters.

Pease feel free to let me know if you need more details.

[Jihwan_Kim]

Please share your sample pbix file's link.

[bushanbo]

Hi Kim,

Sorry for the late reply. Not able to find option to attach the file. Its weird. Let me find it and attach in the next email.

If you see in the last line in red is just text but it should dynamically change based on filter selection like other stories on Smart Narrative.

Earlier the logic we tried is giving one value from this dimesion column but we need to show top 3 values of this column based on filters selection.

[Jihwan_Kim]

Hi, @bushanbo 

If it is OK with you, you can save your sample pbix file in OneDrive or Google Drive, and you can share the link here.

Thank you.

[bushanbo]

Hi Kim,

Can you please check if you can access this 'CSO Forum - Smart Narrative' file and let me know if you see any access issues.

https://drive.google.com/file/d/1T1NQGxbuBb9bZiXppXpSEO9KNHYgFQuO/view?usp=sharing

Thanks

Nag

[Jihwan_Kim · Accepted solution]

Hi, @bushanbo 

Please check the below.

The link to the sample pbix file is down below.

CSO SID count rank 3 =

CONCATENATEX (

FILTER (

ALLSELECTED ( PROBLEM[Root Cause Service] ),

RANKX (

ALLSELECTED ( PROBLEM[Root Cause Service] ),

CALCULATE ( DISTINCTCOUNT ( CSO[CSO_SID] ) ),

,

DESC

) <= 3

),

PROBLEM[Root Cause Service],

", ",

RANKX (

ALLSELECTED ( PROBLEM[Root Cause Service] ),

CALCULATE ( DISTINCTCOUNT ( CSO[CSO_SID] ) ),

,

DESC

)

)

https://www.dropbox.com/s/l0pqjuy5lzhdzja/CSO%20Forum%20-%20Smart%20Narrative.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[bushanbo]

Awesome. Thanks much Kim. Working perfectly as expected.

Take care & Be safe.

Best Regards,

Nag

[Jihwan_Kim]

Hi, @bushanbo 

Please try to write something like below.

smartnarrative top3 countries = concatenatex (TOPN (3, all your countries column, your measure, desc), your country column, ", ")

If it is OK with you, please share your sample pbix file's link, then I can try to look into it to come up with a more accurate measure.

Thanks.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM