Can't sum Total

[Daniel_Fdrvc]

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!

[v-janeyg-msft · Accepted solution]

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.

[parry2k]

@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)

[VijayP]

@Daniel_Fdrvc 

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?

[VijayP]

@Daniel_Fdrvc 

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_Fdrvc]

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

[Daniel_Fdrvc]

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