All the dim table column values are appearing when I add few measures to Table visual

[anushak2207]

I have to calculate categories for Previous year sales. My data is as follow:
Fact Table:

Geo Key	Partner ID	Sales	Year
565APJ	6140	1,266.90	YTD-1
USNA	3402	1,728.22	YTD
USNA	3402	5,667.01	YTD-1
USNA	3455	0	YTD
USNA	3455	2,373.55	YTD-1
USNA	3521	1,517.19	YTD
USNA	3521	3,330.48	YTD-1
USNA	3785	2,256.53	YTD-1

Dim Table:

dim_key	Region
565APJ	ASIA PACIFIC
USNA	AMERICAS
409EMEA	EMEA

Relationship from Fact to Dim is Many to One.

Previous year sales measure is as follows:

PY sales = CALCULATE(

CALCULATE(Sum(Fact[Sales]),  FILTER('Fact','Fact'[Year] = "YTD-1"))
Category is if the  [PY sales] <=1000, then "A", [PY sales] >1000 && [PY sales] <=5000 then "B" else "C"
PY Category = 

SWITCH(

    TRUE(),

    [PY sales] <= 1000 , "A",

    [PY sales] > 1000 && [PY sales] <= 5000, "B", "C")
When I add these 2 measures along with Region column from Dim table, all the values from region values are appearing with blank PY sales.

Partner ID	Region	PY Sales	PY Category
3402	AMERICAS	5,667.01	C
3402	ASIA PACIFIC		A
3402	EMEA		A
3455	AMERICAS	2,373.55	B
3455	ASIA PACIFIC		A
3455	EMEA		A
3521	AMERICAS	3,330.48	B
3521	ASIA PACIFIC		A
3521	EMEA		A

My Expected result should be like below:

Partner ID	Region	PY Sales	PY Category
3402	AMERICAS	5,667.01	C
3455	AMERICAS	2,373.55	B
3521	AMERICAS	3,330.48	B

Kindly help to create the PY Category.

[Anonymous · Accepted solution]

Hi,

Thanks for the solution @amitchandak  offered, and i want to offer some more information for user to refer to.

hello @anushak2207 , you just need to change your PY Category to the following.

PY Category =
IF (
    [PY sales] <> BLANK (),
    SWITCH (
        TRUE (),
        [PY sales] <= 1000, "A",
        [PY sales] > 1000
            && [PY sales] <= 5000, "B",
        "C"
    )
)

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[anushak2207]

Thank You so much! This solution worked for me.

[amitchandak]

@anushak2207 , YTD and YTD -1 are values in table, then better have them in DIM , then it work better

like

PY sales = CALCULATE(
CALCULATE(Sum(Fact[Sales]), FILTER(all('Year'),'Year'[Year] = "YTD-1"))

then this should work

PY Category =
SWITCH(
TRUE(),
[PY sales] <= 1000 , "A",
[PY sales] > 1000 && [PY sales] <= 5000, "B", "C")

if you want to group by A, B, C, refer segmentation

Also refer

Power BI ABC Analysis using Window function, Dynamic Segmentation: https://youtu.be/A8mQND2xSR4