Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.