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
Anonymous
Not applicable

Dax to get 80% contributed products as a single value on card

Hi All,

Can anyone help me in getting the below output on card visual.


80% contribution products.JPG

 

 

 

 

 

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish,

 I am expecting same kind of solution but small change in my dataset.

I do not have index column in my dataset and there are multiple records for each product.

In this scenario, how to create index column for distinct products.

If we can create index column/measure for distinct products, then my problem will be solved.

 

Kindly help me here.

 

Thanks in advance.

 

Regads,

Narasimha

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish,

 

In your solution, the output is coming in alphabetical order. can we get same output based on sales from highest to lowest value.

 

Thanks in advance.

 

Regards,

Narasimha

Hi,

You may download my PBI file from here.  In the card visual, I cannot get the order to be E,B,F.  Someone else will help you with that.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
SteveCampbell
Memorable Member
Memorable Member

It's not going to know the order of your Products, and they may not be named alphabetically, so I recommend adding an index column.

Then, you could use this:

Top 80% = 
VAR _PercentValue = 0.8 //Change to get top N percent
VAR _InEx = 0 // this can be 1 or 0 -- change to 1 to include the value that goes over N% RETURN CONCATENATEX ( TOPN ( COUNTROWS ( FILTER ( ADDCOLUMNS ( 'Table', "rnkCol", VAR _vl = ( 'Table'[Index] ) RETURN DIVIDE ( CALCULATE ( SUM ( 'Table'[Sales] ), ALL ( 'Table' ), 'Table'[Index] >= _vl ), CALCULATE ( SUM ( 'Table'[Sales] ), ALL ( 'Table' ) ) ) ), [rnkCol] <= _PercentValue ) ) + _InEx, 'Table', 'Table'[Sales], DESC ), 'Table'[Product], ", ", 'Table'[Sales], ASC )

Otherwise, to do a Pareto style top 80% of products, you can use:

Top 80% =
VAR _PercentValue = 0.8 //Change to get top N percent
VAR _InEx = 0 // this can be 1 or 0 -- change to 1 to include the value that goes over N% RETURN CONCATENATEX ( TOPN ( COUNTROWS ( FILTER ( ADDCOLUMNS ( 'Table', "rnkCol", VAR _vl = ( 'Table'[Sales] ) RETURN DIVIDE ( CALCULATE ( SUM ( 'Table'[Sales] ), ALL ( 'Table' ), 'Table'[Sales] >= _vl ), CALCULATE ( SUM ( 'Table'[Sales] ), ALL ( 'Table' ) ) ) ), [rnkCol] <= _PercentValue ) ) + _InEx, 'Table', 'Table'[Sales], DESC ), 'Table'[Product], ", ", 'Table'[Sales], ASC )

 

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  linkedin-logo.png
Read my blogs on  powerbi.tips_.png



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



Anonymous
Not applicable

Hey Steve,

 

I do not have index column in my dataset. can we create an index column using DAX.

Also i have mutiple rows of data for each product.

can we assign index for distinct products.

 

Thanks in advance.

 

Regards,

Narasimha

 

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.