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 a...
Need Help
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.