Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Can anyone help me in getting the below output on card visual.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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.
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
Read my blogs on
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
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.
Regards,
Narasimha
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |