Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Relationship - value mismatch

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

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • m3tr01d's avatar
    m3tr01d
    Continued Contributor
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

       

      Hi,  m3tr01d  kindly find the below image

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

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

    Measure1 = distinctcount(Data[Category])

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        To your table visual, drag Category and Totals1

        Totals = min(Data[Total])

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

        Hope this helps.