Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Count Total, based on condition from other table

Hi all,

 

I have a question. I want to counter the numbers of orders per categorie (Groups[Total amount between]).

I have 2 tables. 1 table is about the sales with the total amounts per order. The other table is about the groupings. An amount of 5.50 falls in the category 5.01 - 7.50 for example. What I want is to count the number of orders per category. So how often have orders been placed in the different categories.
I've been stuck for several days. I only get the total orders....

Here's my data.

I have many rows. This is a small part of the data.

Groupings table

KeyMinMaxTotal amount between
1050 - 5 euro
25.017.505.01 - 7.50 euro
37.51107.51 - 10.00 euro
410.0112.510.01 - 12.50 euro
512.511512.51 - 15.00 euro
615.0117.515.01 - 17.50 euro

 

Sales table:

Only Total amount is relevant in this table.

KeyOrder AmountDonation AmountTotal amount
............2.50
............2.50
............2.65
............5.50
............5.50
............7.81
............5.50
............8.00
............8.95
............9.00
............422.50
............424.00

 

What I want:

Total amount between Count
0 - 5.00 3
5,01 - 7,50 3
7.51 - 104
..................
420.01 - 422.501
422.51 - 425.501

 

My measure now and what I get:

Count = CALCULATE(
COUNTROWS(Sales), FILTER(Groups, Sales[Total] >= Groups[Min] && Sales[Total] <= Groups[Max]))
 
s1107982_0-1622452217374.png

 

My example Power BI File:

https://www.dropbox.com/s/u0b9tin0u4olb2t/PowerBiToCommunityQuestion2.pbix?dl=0

 

I hope someone can help me, I have a deadline today.

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

Hey @Anonymous ,

 

you should set the min and max as variable. Like this you don't have the confusion with row and filter contexts.

Try the following approach:

Count = 
VAR vMin = MIN( Groups[Min] )
VAR vMax = MAX( Groups[Max] )
RETURN
    CALCULATE(
        COUNTROWS( Sales ),
        FILTER(
            Sales,
            Sales[Total amount] >= vMin && Sales[Total amount] <= vMax
        )
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

1 REPLY 1
selimovd
Super User
Super User

Hey @Anonymous ,

 

you should set the min and max as variable. Like this you don't have the confusion with row and filter contexts.

Try the following approach:

Count = 
VAR vMin = MIN( Groups[Min] )
VAR vMax = MAX( Groups[Max] )
RETURN
    CALCULATE(
        COUNTROWS( Sales ),
        FILTER(
            Sales,
            Sales[Total amount] >= vMin && Sales[Total amount] <= vMax
        )
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.