Forum Discussion
Helper ICan'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!
Hi, Daniel_Fdrvc
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)If you still have problems, please feel free to ask me.
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
- VijayP
Community Champion
If possible share the pbix file without senstive info. You are delaing with two different aggreation count and sum which may be causing this!
Daniel_FdrvcVijayPi 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!
- Daniel_Fdrvc
amitchandak / Greg_Deckler / parry2k / MFelix / Ashish_Mathur maybe someone have solved same task earlier and can help me? Sorry for mentions 😉
- VijayP
Can you tell me why you are first aggregating as count and wanted to convert that to SUM, it is contradictory right?
- Daniel_Fdrvc
VijayPmaybe this problem appears because i'm calculating in matrix, not in table? Maybe this can help something?
- parry2k
Super User
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
- Daniel_Fdrvc
In the same matrix table, like a third column:
1GD
2GD
Third: IF((1GD + 2GD) > 2,(1GD*1+2GD*2),0)
- v-janeyg-msft
Community Support
Hi, Daniel_Fdrvc
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)If you still have problems, please feel free to ask me.
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.
