Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Narender
Resolver I
Resolver I

Total sum of measure showing wrong data

Hi All,

 

I have 2 measures and i am creating 3rd measure based on measure1 and measure2. But Measure3 showing wrong total sum. 

Measure1 = CALCULATE (
SUM(Table1[AMOUNT]))

 

Measure2 = has static value  100 for each row

 

Measure3 = CALCULATE(IF([Measure 3]>[Measure2],[Measure 3],0))

 

 

Result is shown below:

 

Tax Group       Measure1         Measure2              Measure3

A                      200                   100                          200

B                        300                   100                       300

C                        50                     100                        0

D                         50                     100                        0

 Total               ------                  ------                     --------

                          600                     400                       600

 

 

Please tell me, how can i show exact total??

 

 

Thanks,

 

Narender

 

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

Hi @Narender

 

Please try this calculated measure

 

Measure3 = SUMX(VALUES('Table1'[Tax Group]),IF([Measure1]>[Measure2],[Measure1],0))

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Phil_Seamark
Employee
Employee

Hi @Narender

 

Please try this calculated measure

 

Measure3 = SUMX(VALUES('Table1'[Tax Group]),IF([Measure1]>[Measure2],[Measure1],0))

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phil,

 

Thanks a alot. Its working .

 

Can you please descriibe about your dax expression?

 

 

Thanks,

 

Narender

HI @Narender

 

In this case, I am using one of the DAX iterator functions.  SUMX will loop as many times as there are rows in the table passed as the first argument.  This is how I can force the total row to be a sum of a series of smaller calculations, rather than just using the total values from the other columns.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.