Table visualisation with percentage of row above

[Ritaf]

Hi,

I need to create the logic of the Funnel Chart on the table,
to show category, value an %, in a way that the first row is 100% and others are the percentage of the row above like on the picture I attached. is there is a way to make it happen dynamically?
Link to Pbix sample fiile 

[lbendlin]

Power BI has no concept of "Row Above" - you need to help it by providing indexes, or by hard coding the logic (for example "Sales as percentage of Calls".

 If you paste the sample data into a table in your post or use one of the file services it will be easier to work with. 

[Ritaf]

Hi, 
Thank you for your response I know that Power BI has no concept of "Row Above", not in visualization at less, and have no doubt that this isn't standard logic. 
There is a link for the sample file:https://drive.google.com/file/d/15dHsxNWd7Fh1Q-dhIaG9uh28r89cmFwr/view
I hope somebody has an idea how to solve it dynamically without measure for each category and "Switch" games.

[Anonymous · Accepted solution]

Hi @Ritaf ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

1. Create a calculated column as below to get the rank base on the sum of values group by category

Rank = 
RANKX (
    ALLSELECTED ( 'Sheet1'[Category] ),
    CALCULATE ( [test], ALLEXCEPT ( 'Sheet1', 'Sheet1'[Category] ) )
)

2. Create a measure as below to get the percentage

% = 
VAR _selcat =
    SELECTEDVALUE ( 'Sheet1'[Category] )
VAR _selindex =
    SELECTEDVALUE ( 'Sheet1'[Rank] )
VAR _prerank =
    CALCULATE (
        MAX ( 'Sheet1'[Rank] ),
        FILTER ( ALLSELECTED ( 'Sheet1' ), 'Sheet1'[Rank] < _selindex )
    )
VAR _pretest =
    SUMX ( FILTER ( ALLSELECTED ( 'Sheet1' ), 'Sheet1'[Rank] = _prerank ), [test] )
RETURN
    IF ( _selindex = 1, 1, DIVIDE ( [test], _pretest ) )

Best Regards