Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Table (Row level calculation)

Hi Team  I have 6 columns ex:column 1,column 2 ,column 3,column 4 ,column 5 ,column 6..I want to add in power bi table visual  I want filter  visual should contain column names as filter 1:Column 1 and column 2 as values to select  and filter 2 should have column 3 and column 4 as value to select and table visual out put should be as below  Column 1+column 2 column 6 column 3 +column 4 column 7 Data is getting divided beacuse as per power bi it wont accept row level calculation ..Can you please guide anyone it could be great help Thanks Nandish

Need Help

3 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Anonymous ,

     

    Can you give some example with dummy data ?

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Please check if my undersatnding is correct.

     

    I think you are having trouble in the following example.

     

    Sample data is as follows.

     

    You want to calculate the sum of the values of A and B. Then you split 2019 and 2020 to get the following table. At last you get the sum of 2019 and 2020 as follows.

    SUM = MAX('Table (3)'[2019])+MAX('Table (3)'[2020])

     

     

    Actually, you don’t need to do this. You can use ALLEXCEPT function to calculate in the first table. You can write your measure like so.

    Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    ALLEXCEPT ( 'Table', 'Table'[User] )
)

     

    If you still need the filter with column names. You can create a new table as below.

    Then you can write a measure.

    Value/Year = 
VAR tt =
    SELECTEDVALUE ( 'Table (2)'[Column1] )
RETURN
    SWITCH ( tt, "Value",MAX('Table'[Value]), "Year",MAX('Table'[Year]
))

    You can check more details from here.

     

     

    Best Regards,

    Icey

     

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