cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bushanbo
Frequent Visitor

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

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.

 

1 ACCEPTED SOLUTION

Hi, @bushanbo 

Please check the below.

The link to the sample pbix file is down below.

Picture4.png

 

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
)
)
 
 
 

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

 


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

10 REPLIES 10
bushanbo
Frequent Visitor

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], " , ")
 

bushanbo_0-1619327356553.png

 

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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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.

bushanbo_1-1619345045796.png

 

 

Please share your sample pbix file's link.


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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.

 

bushanbo_0-1619455152862.png

 

 

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.


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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

Hi, @bushanbo 

Please check the below.

The link to the sample pbix file is down below.

Picture4.png

 

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
)
)
 
 
 

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

 


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Awesome. Thanks much Kim. Working perfectly as expected.

Take care & Be safe.

 

Best Regards,

Nag

 

Jihwan_Kim
Super User
Super User

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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.