cancel
Showing results for
Did you mean:
Frequent Visitor

## Can't sum Total

Hi all,

here's a main problem: i need to SUM peoples that ar in different categories.

First I have created a measure that calculates me amount of sales (left table):

GD sutartys = CALCULATE(COUNT(Ranking[Product]),FILTER(Ranking,OR(Ranking[RodzPolisy]="BN",OR(Ranking[RodzPolisy]="FPP",OR(Ranking[RodzPolisy]="JUV",OR(Ranking[RodzPolisy]="PEN",OR(Ranking[RodzPolisy]="B1",Ranking[RodzPolisy]="B2")))))))

Then i have created two measures, that shows me to which category is assigned a seller (right table):

• 1GD = IF([GD sutartys]=1,1,0)
• 2+GD = IF([GD sutartys]>1,1,0)

And then, i need to sum peoples to know how many of them is in 1GD and how many in 2+GD categories separate. And this is a really strange thing - i fail. SUM of this information is required to show certain unit manager how many sellers is in each category. Based on that SUM information i'll create another measure that will check IF(SUM(1GD)+SUM(2+GD)>5) if true, then do this, else do that.

Hope you will help me 😉 thx in advance!

1 ACCEPTED SOLUTION
Community Support

According to your description, I modify your two measures and create a new measure to show the result you want.

Like this:

``````1 =
VAR a =
SUMMARIZE (
Sales,
Sales[Date],
Sales[GD],
Sales[ID],
"1", IF ( SUM ( Sales[GD] ) = 1, 1, 0 )
)
RETURN
SUMX ( a, [1] )``````
``````2 =
VAR a =
SUMMARIZE (
Sales,
Sales[Date],
Sales[GD],
Sales[ID],
"2", IF ( SUM ( Sales[GD] ) > 1, 1, 0 )
)
RETURN
SUMX ( a, [2] )
``````
``3 = IF([1]+[2]>2,[1]+[2]*2,0)``

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 REPLIES 8
Community Support

According to your description, I modify your two measures and create a new measure to show the result you want.

Like this:

``````1 =
VAR a =
SUMMARIZE (
Sales,
Sales[Date],
Sales[GD],
Sales[ID],
"1", IF ( SUM ( Sales[GD] ) = 1, 1, 0 )
)
RETURN
SUMX ( a, [1] )``````
``````2 =
VAR a =
SUMMARIZE (
Sales,
Sales[Date],
Sales[GD],
Sales[ID],
"2", IF ( SUM ( Sales[GD] ) > 1, 1, 0 )
)
RETURN
SUMX ( a, [2] )
``````
``3 = IF([1]+[2]>2,[1]+[2]*2,0)``

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User III

@Daniel_Fdrvc it is not clear where you want to see the total based on the calculation you provided:

Based on this SUM OF TOTAL in each month i need to calculate IF((1GD + 2GD) > 2,(1GD*1+2GD*2),0). For example in january there should be: 1+2 = 3 > 2, so 1*1+2*2 = 5

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

In the same matrix table, like a third column:

1GD

2GD

Third: IF((1GD + 2GD) > 2,(1GD*1+2GD*2),0)

Super User I

Can you tell me why you are first aggregating as count and wanted to convert that to SUM, it is contradictory right?

MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Frequent Visitor

@VijayPmaybe this problem appears because i'm calculating in matrix, not in table? Maybe this can help something?

Super User I

If possible share the pbix file without senstive info. You are delaing with two different aggreation count and sum which may  be causing this!

MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Frequent Visitor

@VijayPi found an opportunity to share the pbix file: https://drive.google.com/file/d/1BROeNvGjvovmpRrooyhPk3WUSNZWB4qf/view?usp=sharing

Maybe now you'll find some time to help me. Thank you in advance!

Frequent Visitor

@amitchandak / @Greg_Deckler / @parry2k / @MFelix / @Ashish_Mathur maybe someone have solved same task earlier and can help me? Sorry for mentions 😉

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.