TOPN/ others with Tableau LOD to Power BI

[niteshtrehan89]

Hi Techies,

Happy New Year.

I am stucked into an urgent requirement which requires migration of a tableau asset to Power BI. In Tableau, I have a measure as

LOD Sales= {fixed (employee name): avg(sales)}

I want to convert this measure into Power BI. 

Secondly, I need to plot a bar chart between the Product category and LOD Sales on the basis of dynamic TOP N / Others.

[v-rzhou-msft]

Hi @niteshtrehan89 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft]

Hi @niteshtrehan89 

Due to I don't know your data model, I build a sample to have a test.

Measure:

Measure = 
CALCULATE(AVERAGE('Table'[Sales]),ALLEXCEPT('Table','Table'[Employee Name]))

For more details convert tableau to measure, you may refer to this blog: Tableau to Power BI: FIXED LOD Expressions

Result is as below.

If you want to use TopN, you can use TopN function in your visual as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[niteshtrehan89]

Hi Rico, 

I need to create the TOP N and Other Analysis dynamically. 

[v-rzhou-msft]

Hi @niteshtrehan89 

What does dynamic mean? Do you want to show your result dynamically by slicer?

Measure is a good way to show dynamic result.

Please share a sample to me by your Onedrive for Business.

Could you show a screenshot of the result you want?

This may make it easier for me to understand your requirement.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[niteshtrehan89]

On the basis of slicer select i need the top n and others values 

[v-rzhou-msft · Accepted solution]

Hi @niteshtrehan89 

I have a test by the sample I show you before.

Build a TopN table to build a slicer.

TopN = GENERATESERIES(1,DISTINCTCOUNT('Table'[Employee Name]))

Measure:

Transform Tableau :LOD Sales= {fixed (employee name): avg(sales)}

into measure Avg.

Avg = CALCULATE(AVERAGE('Table'[Sales]),ALLEXCEPT('Table','Table'[Employee Name]))

Measure = 
VAR _TopN = IF(ISFILTERED('TopN'[Value]),SELECTEDVALUE('TopN'[Value]),MAX('TopN'[Value]))
Var _T1 = TOPN(_TopN,SUMMARIZE(ALL('Table'),'Table'[Employee Name],"Avg",[Avg]),[Avg],DESC)
Return
SUMX(FILTER(_T1,[Employee Name]=MAX('Table'[Employee Name])),[Avg])

Result is as below.

By default it will show all values.

If you select Top1, it will only show A in visual.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@niteshtrehan89 , Try measures  like

Avg Sales= AverageX(values(Table[employee]), calculate(Sum(Table[Sales])))

Top 10 Rank = CALCULATE([Avg Sales],TOPN(10,all(Table[Product category]),[Avg Sales],DESC),VALUES(Table[Product category]))

TOPN with others : https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

[niteshtrehan89]

Hi Amit, 

Top N function is not working fine and is not matching with the values.