I have a visualization in my report made by data table that pulls entirely from a single table.

The contents of this table contains several calculated columns which are written in DAX.  One among these columns takes the sum of the previous 3 columns and then returns a value based on a SWITCH statement.

I am trying to attempt 'SUM'  of the values in this SWITCH statement column in order  to get the total at the bottom. Earlier used the following measure to calculate the SUM

```Cyclestar Pay Real Values =
IF(COUNTROWS(VALUES(ClassSchedule[ID of Class]))=1,
[CycleStar Pay],
SUMX(VALUES(ClassSchedule[ID of Class]), [CycleStar Pay]))```

I am not able to figure out how to sum the values in this column correctly.

May 16, 2019 in Power BI
edited May 17, 2019 7,093 views

## 1 answer to this question.

It's a good practice if you avoid the measures that are designed keeping specific visualization in mind.

Let us consider an example based on the data provided above.

1. The years are identifiers for the rows in the table

2. If any other column or row is added like month then the behavior of the measure will be different

The part of your formula for the calculation the total measure would work  well on the detail rows when iterating over VALUES(Table[Year] instead of Table itself:

`MyMeasure3 = SUMX(FILTER(VALUES(Table[Year]),[Amount]>1000),[Amount]-1000)`

However, VALUES(Table[Year]) would contain only one row while the filtered table is empty when [Amount]<=1000.

This means that rows for years with [Amount] lower than 1000 should have a blank value. In case you want to have 0 instead of blank, you can just add 0 to the result:

`MyMeasure3 = SUMX(FILTER(VALUES(Table[Year]),[Amount]>1000),[Amount]-1000) + 0`

Hope this helps!!

If you are interested in learning Power BI, check out Power BI Curriculum now!

• 1,520 points

edited May 17, 2019 by Omkar

+1 vote

## How to create a relative data slicer in Power BI?

Hi, With the relative date slicer or filter, you ...READ MORE

## How to troubleshoot a refresh on data set in power bi service

Hello, @vnk, You may see an error when ...READ MORE

## Install Power BI Desktop

It’s a pretty simple process. All you ...READ MORE

## How do I format the KPI in Power BI

format the KPI by selecting the paint ...READ MORE

+1 vote

## Difference between Power BI Desktop client and web client

One of the reason I rely on ...READ MORE