cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ranga_88
Helper I
Helper I

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 - source.PNG

 

after unpivot table looks 

unpivot.PNG

 

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

 

Thanks

Ranga

1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

Hi @ranga_88 ,

 

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:

sum distinct values.PNG

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.

View solution in original post

6 REPLIES 6
Eyelyn9
Community Support
Community Support

Hi @ranga_88 ,

 

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:

sum distinct values.PNG

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.

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

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

Measure1 = distinctcount(Data[Category])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 Capture1.PNG

Hi,

To your table visual, drag Category and Totals1

Totals = min(Data[Total])

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
m3tr01d
Responsive Resident
Responsive Resident

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

 

Hi,  @m3tr01d  kindly find the below image

 

Capture2.PNG

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors