3 file with common file with price range parameter

[Anonymous]

Hi Friends,

I have 3 files and 1  of this is connecting file. The masterfile (relationship). 

Now, 1 need a DAX that will determine or create report base on different category with different  price range base.  using this 3 file

Category will be the basis of price range. 

i want get total sold qty per category per price range. Hope you can help me.

FIRST FILE (MASTERFILE)

FILE 2 (with relationship in file 1 using item column)

FILE 3

now need a DAX that will determine or create report base on different category with different  price range base.  using this 3 file

Category will be the basis of price range. 

i want get total sold qty per category per price range. Hope you can help me.

thank you so much in advance,

[Anonymous]

Hi Friends,

I have 3 files and 1  of this is connecting file. The masterfile (relationship). 

Now, 1 need a DAX that will determine or create report base on different category with different  price range base.  using this 3 file

Category will be the basis of price range. 

i want get total sold qty per category per price range. Hope you can help me.

FIRST FILE (MASTERFILE)

FILE 2 (with relationship in file 1 using item column)

FILE 3

now need a DAX that will determine or create report base on different category with different  price range base.  using this 3 file

Category will be the basis of price range. 

i want get total sold qty per category per price range. Hope you can help me.

thank you so much in advance,

[Greg_Deckler]

Can you post this as data that can be copied and pasted? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi @Greg_Deckler,

I have 3 files and 1  of this is connecting file. The masterfile (relationship). 

Now, 1 need a DAX that will determine or create report base on different category with different  price range base.  using this 3 file

FIRST FILE:

ITEM         CATEGORY         CATEGORY 2

APPLE        FRUITS               ORANGE

BANANa    FRUITS                YELLOW

GRAPES     FRUITS                PURPLE

CHEERY     FRUITS                RED

CAT          ANIMAL               BLACK

DOG         ANIMAL               WHITE

DOVE       ANIMAL               WHITE

FISH         ANIMAL               ORANGE

DRESS     CLOTHES              BLUE

PANTS     CLOTHES             DEMIN

SKIRT       CLOTHES             GREEN

GOWN     CLOTHES             YELLOW

FILE 2 (with relationship in file 1 using item column)

ITEM               PRICE                  QTY SOLD       

APPLES           300                        4                        

BANANA        450                        2       

DOG               15000                    8

CAT                 700                        2

PANTS              50                       7

GOWN              1000                  10             

FILE 3

CATEGORY         RANGES                     MIN                MAX

FRUITS                 ENTRY                         0                  300

FRUITS                 MID                            301               999

FRUITS                 HIGH                         1000                above

ANIMAL               ENTRY                         0                  5000

ANIMAL               MID                           5001            10000

ANIMAL               HIGH                         10001           ABOVE

CLOTHES             ENTRY                          0                 500

CLOTHES             MID                             501              5000

CLOTHES             HIGH                           5001            ABOVE

Category will be the basis of price range. 

i want get total sold qty per category per price range. Hope you can help me.

[v-chuncz-msft]

@Anonymous,

You may refer to the measure below.

Measure =
SUMX (
    FILTER (
        Table2,
        RELATED ( Table1[CATEGORY] ) = SELECTEDVALUE ( Table3[CATEGORY] )
            && Table2[PRICE] >= SELECTEDVALUE ( Table3[MIN] )
            && (
                ISBLANK ( SELECTEDVALUE ( Table3[MAX] ) )
                    || Table2[PRICE] <= SELECTEDVALUE ( Table3[MAX] )
            )
    ),
    Table2[ QTY SOLD]
)
    + 0

[Anonymous]

Hi @v-chuncz-msft,

solution is invalid. 

1. i cant create relationship the 2 table table 2 and 3 reason no unique values.

Please help. DO i need to rearrange my price range table?

thank you,

Eannecute

[v-chuncz-msft]

@Anonymous,

The relationship is between Table1 and Table2.

[Anonymous]

Hi @v-chuncz-msft,

Error encountered,

cant display the visual.

Thank you,

Eannecute