cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ruesaint_denis
Helper I
Helper I

Calculated Measure not Summing

Hi everyone, 

 

I have a calculated measure and for some reason it's not summing correctly. This is my measure, and below you can see the results: 

Linqto Bucks Promotional Cost = 
Var true_24 = calculate(COUNTA(Orders[24 Rolling]), Orders[24 Rolling]=TRUE(), Orders[Payment Type]="Wire")
return
IF(true_24 = 2, 1000, IF(true_24=3, 2000, IF(true_24>3, 5000, 0)))

Orders[24 Rolling] is a Boolean True/False Column. 

 

I am trying to see if there's X amount of Orders[24 Rolling] = True && Orders[Payment Type]="Wire" , then assign certain value. 

 

ruesaint_denis_0-1643334468131.png

 

You can see the sum isn't working at the end. Any ideas? 

1 ACCEPTED SOLUTION

This worked, I used this website to reach my solution: 

https://finance-bi.com/blog/power-bi-totals-incorrect/

Linqto Bucks Promotional Cost = 
var summarizedTable = ADDCOLUMNS ( 
SUMMARIZE ( Orders, Orders[User Id]), 
"Qualifying Orders", CALCULATE ( COUNTA(Orders[24 Rolling]), Orders[24 Rolling]=TRUE(), Orders[Payment Type]="Wire"))
return
SUMX(summarizedTable,
IF([Qualifying Orders]=2, 1000, 
IF([Qualifying Orders]=3, 2000,
IF([Qualifying Orders]>3, 3000,BLANK()))))

 

 

View solution in original post

3 REPLIES 3
VahidDM
Super User
Super User

Hi @ruesaint_denis 

 

Can you share a samole of your data table in a text format. BTW, check this link:

https://www.vahiddm.com/post/why-my-measure-returns-the-wrong-total

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Hi @VahidDM , 

 

I am really not getting it. My data looks as the following: 

User IDOrder ID24hr RollingPayment Method
24100TrueUphold
30105FalseWire
45200TrueWire
45204TrueWire
24101TrueUphold
45303TrueWire

 

The desired output is the following: 

- filter for orders where payment method = wire, and 24hr rolling = True, groupby user ID

- calculate promotional cost with different IF statements 

 

if count of 2hr rolling = 2, then 1000

if count of 2hr rolling = 3, then 2000

if count of 2hr rolling > 3, then 5000

User IDSum of Count of 24hr Rolling 
where 24hr rolling = True
Promotional Cost
4532000

 

I am able to calculate the appropriate promotional cost for each user ID, but the summing isn't working using this DAX measure: 

 

 

Linqto Bucks Promotional Cost = 
Var true_24 = calculate(COUNTA(Orders[24 Rolling]), Orders[24 Rolling]=TRUE(), Orders[Payment Type]="Wire")
return
IF(true_24 = 2, 1000, IF(true_24=3, 2000, IF(true_24>3, 5000, 0)))

 

 

This worked, I used this website to reach my solution: 

https://finance-bi.com/blog/power-bi-totals-incorrect/

Linqto Bucks Promotional Cost = 
var summarizedTable = ADDCOLUMNS ( 
SUMMARIZE ( Orders, Orders[User Id]), 
"Qualifying Orders", CALCULATE ( COUNTA(Orders[24 Rolling]), Orders[24 Rolling]=TRUE(), Orders[Payment Type]="Wire"))
return
SUMX(summarizedTable,
IF([Qualifying Orders]=2, 1000, 
IF([Qualifying Orders]=3, 2000,
IF([Qualifying Orders]>3, 3000,BLANK()))))

 

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!