cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jatneerjat Member
Member

Create calculate row

Hi,

 

How can i add 2 rows in my table in which one row shows sum all values as 'T' and other row will shows sum of only values in bottom 4 rows as 'F'.

Here stage is coming from a table and 'current','previous','%change' are measures

 

1.PNG

2 REPLIES 2
Community Support Team
Community Support Team

Re: Create calculate row

HI, @jatneerjat 

After my research, you could try this way:

Step1:

Add a Stage table with two more rows like thisSmiley SadIn my simple case, I just show top2 result as "F")

Then create a relationship with basic data table.

4.JPG5.JPG

Step2:

Create the rank measure for each measure

Rank = RANKX(ALL('Table1'), [sumqty])

Step3:

Add a new measure for 'current'

Measure = 
IF (
    SELECTEDVALUE ( Table2[Stage] ) = "TOTAL",
    CALCULATE ( [sumqty], ALL(Table2[Stage]) ),
    IF (
        SELECTEDVALUE ( Table2[Stage] ) = "TOP2",
        SUMX (
            FILTER ( ALL ( 'Table2'[Stage] ), [Rank] <= 2 ),
            [sumqty]
        ),
        [sumqty]
    )
)

Result:

6.JPG

You could also add new measure for 'previous','%change' by the same logic.

 

here is pbix file, please try it.

and this is the same logic post I have solved for you refer to

https://community.powerbi.com/t5/Desktop/Top-N-and-Others-monthly/m-p/490708#M228672

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
jatneerjat Member
Member

Re: Create calculate row

Thanks a lot @v-lili6-msft .

 

Let me try if it works.