Relationship - value mismatch

[Anonymous]

Hi,

I have a table of 20 fields ,  i have duplicated the same table unpivot 5 columns  after completing these 2 tables i have created the relationship ,   but value is mismatching how to fix this issue

2)  after unpivot how to remove the duplicate  values

Actual table - 

after unpivot table looks 

if i count the each category  it will return as a 3 but actual value is 1 how to solve this?

Thanks

Ranga

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

Please try this:

1. Add rank measure based on each Category and each Total value:

Rank = RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[Category] = MAX ( 'Table'[Category]) && 'Table'[Total]=MAX('Table'[Total]) ),
    CALCULATE ( MAX ( ( 'Table'[Value]) ) ),
    ,
    DESC
)

2. Sum values whose rank=1:

Sum = CALCULATE(SUM('Table'[Total]),FILTER('Table',[Rank]=1))

The final output is shown below:

If it does not make sense, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

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

[Ashish_Mathur]

Hi,

To your visual drag Category to the visual and write this measure

Measure1 = distinctcount(Data[Category])

Hope this helps.

[Anonymous]

how to find the Distinct Sum  i try to use sumx suppose the value repeat means it will take only one value 

for example 1 is repeating multiple place means it will take only one time how to achieve this

Kindly see the below screen shot  Total colum actula value is  : 3.7   but im getting : 11.1 

[Ashish_Mathur]

Hi,

To your table visual, drag Category and Totals1

Totals = min(Data[Total])

Totals1 = SUMX(values(data[category]),[Totals])

Hope this helps.

[m3tr01d]

@Anonymous Do you have a picture of the visual you would like to make with this data?

[Anonymous]

Hi,  @m3tr01d  kindly find the below image