How to sum a data table in Power BI?

0 votes

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.

6 days ago in Power BI by Phalguni
• 750 points

edited 4 days ago by Omkar 21 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

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!!

answered 4 days ago by Avantika
• 1,060 points

edited 4 days ago by Omkar

Related Questions In Power BI

0 votes
1 answer

Is there a way to convert JSON data to readable table in power bi

Follow these steps step 1 - Click on ...READ MORE

answered Sep 24, 2018 in Power BI by Hannah
• 14,080 points
1,097 views
0 votes
1 answer

How to combine your data or tables in Power BI?

Power BI includes Query Editor which is ...READ MORE

answered Feb 27 in Power BI by Phalguni
• 750 points
20 views
0 votes
1 answer

How to create final table based on Joins of two tables in power BI?

Hi Nithin, It is easy and simple. 1. Go ...READ MORE

answered Mar 1 in Power BI by CHERUKURI
• 13,380 points
58 views
0 votes
1 answer

How do you add a slicer from different table in Power BI?

In order to add a slicer from ...READ MORE

answered Mar 5 in Power BI by Phalguni
• 750 points
23 views
0 votes
1 answer

Install Power BI Desktop

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

answered Oct 9, 2018 in Power BI by Kalgi
• 35,750 points
28 views
0 votes
1 answer

Few tips before I start creating Power BI dashboard

It’s always advisable to begin with the data ...READ MORE

answered Oct 9, 2018 in Power BI by Kalgi
• 35,750 points
54 views
0 votes
1 answer

How do I format the KPI in Power BI

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

answered Oct 9, 2018 in Power BI by Kalgi
• 35,750 points
38 views
+1 vote
1 answer
0 votes
1 answer

How do you create a link visual in Power BI?

You can easily create a link between ...READ MORE

answered Mar 8 in Power BI by Avantika
• 1,060 points

edited Mar 9 by Avantika 34 views
0 votes
1 answer

How To use R Integration in Power BI?

Hi,  There are a lot of packages available ...READ MORE

answered May 2 in Power BI by Avantika
• 1,060 points
23 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.