cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daniel_Fdrvc
Frequent Visitor

Can't sum Total

Hi all,

 

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

help.png

 

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
v-janeyg-msft
Community Support
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)

v-janeyg-msft_0-1621930183436.png

 

v-janeyg-msft_1-1621930198641.png

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.

View solution in original post

8 REPLIES 8
v-janeyg-msft
Community Support
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)

v-janeyg-msft_0-1621930183436.png

 

v-janeyg-msft_1-1621930198641.png

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.

View solution in original post

parry2k
Super User III
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

 

 






Did I answer your question? Mark my post as a solution.

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





In the same matrix table, like a third column:

 

1GD

2GD

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

VijayP
Super User I
Super User I

@Daniel_Fdrvc 

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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

VijayP
Super User I
Super User I

 

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors