Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Hi,
To your visual drag Category to the visual and write this measure
Measure1 = distinctcount(Data[Category])
Hope this helps.
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
Hi,
To your table visual, drag Category and Totals1
Totals = min(Data[Total])
Totals1 = SUMX(values(data[category]),[Totals])
Hope this helps.
@Anonymous Do you have a picture of the visual you would like to make with this data?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |