Multiple rows are showing instead of displaying the score in one row for each accounts

[WinterGarden]

Hi ,
I am displaying few columns from different tables in a table visual in pbi, refer below screenshot.

I need to calculate the score of each account based on "GTER Penetration" values..
when i tried to add that column, while calculating the score for gter penetration multiple rows are getting added for each account ie. it is automatically populating all the sector,area, region values in the table, which results in duplicate rows..

i am using columns from multiple tables
1) FiscalYear,FiscalYear-Month,UDN,Account : from "Disclaimer" table
2) Sector : from "Sector" table
3) Area,Region : from "Area-Region" table
3) GTER : is from "GTER_Data" table
4) GTER Penetration: from "Alliance_Data" table

Relationships are created in the below order:
GTER_Data(Customkey eg: 3M CompanyFY24Q2) --------Disclaimer(customkey) :(many to one, single)
GTER_Data(SectorDesc)------Sector(SectorDesc) :(many to one, single)
GTER_Data(AreaRegion)------Area-Region(AreaRegion) :(many to many, single(Area-Region filters GTER_Data) )
Alliance_Data(Customkey eg: 3M CompanyFY24Q2) ------Disclaimer(customkey) :(One to One, both)

My query is:
1) since there is no direct relationship between Disclaimer  and Sector ,  Disclaimer  and Area-Region, how the sector , area, region values are displaying correctly in the table like in screenshot 1?
2) how to calculate the score value for each accounts without getting duplicate/unwanted rows..

score value is claculated based on each range GTER Penetration falls under..

[Anonymous]

Hi @WinterGarden ,

I create five tables as you mentioned.

I also make some relationships.

Then I think you can create a calculated column and here is the DAX code.

GTER Penetration Score = 
SWITCH(
    TRUE(),
    'Alliance_Data'[GTER Penetration] < 0.2, 1,
    'Alliance_Data'[GTER Penetration] < 0.4, 2,
    'Alliance_Data'[GTER Penetration] < 0.6, 3,
    'Alliance_Data'[GTER Penetration] < 0.8, 4,
    'Alliance_Data'[GTER Penetration] <= 1, 5,
    BLANK()
)

You can also create a measure and then you can get what you want.

Total Score = 
SUMX(
    VALUES('Disclaimer'[Account]),
    CALCULATE(
        MAX('Alliance_Data'[GTER Penetration Score])
    )
)

Best Regards

Yilong Zhou

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

[WinterGarden]

Hi @Anonymous ,

Thank you for taking your time to help resolve this issue. Much appreciated.
This is the issue i am facing..
if i add those main columns into a table this is how it looks like:

in this visual you can see, single row is created for both accounts A1 , A2
where as if i add that total score measure, duplicate rows are created

UDN,Account : from disclaimer
SectorDesc : from sector
Area, Region : from Area-Region
GTER: from GTER_Data
GTER Penetration,total score : from Alliance_Data

my desired output is:

UDN	Account	SectorDesc	Area	Region	GTER	GTER Penetration	Total Score
U1	A1	Sector1	Area1	Region1	0.5	0.5	3
U2	A2	Sector2	Area2	Region2	0.7	0.7	4

do you know how i can achieve that?

to resolve this issue i tried to create relationship between Alliance_Data and Sector, Alliance_Data and Area-Region tables same as GTER_Data table, after adding the sector and arearegion columns in Alliance_Data table like in the below screenshot:

but it is causing ambiguity issue: