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.
I have a dataset which i need to add a column that labels whether or not a store location is unique. When a store has multiple products my data is providing the total units for all products in the column next for both products, effectively doubling the number. So for WalmartDallas, only 100 total products were sold - it may have been 70 apples and 30 bananas, we dont know. But if you were to sum the total units column, it would appear this store sold 200 total units which is incorrect.
What i wan to create is the highlighted column. I know for specific calculations I could just do an average total units per distinct store-city, but for other applications it would be eaisier to have the identifying column.
Solved! Go to Solution.
Infact the formula can be reduced to following. If you need result as Binary, you can chantge the data type to Binary from the Modelling tab>>formatting section. 1 is equivalent to TRUE, 0 is equivalent to FALSE
Column Formula = VAR myrank = RANKX ( CALCULATETABLE ( 'TableName', ALLEXCEPT ( 'TableName', 'TableName'[Store], 'TableName'[City] ) ), [Product], , ASC, DENSE ) RETURN IF ( myrank = 1, 1, 0 )
Try this as a Calculated Column
Column = VAR IsUnique = CALCULATE ( COUNTROWS ( 'TableName' ), ALLEXCEPT ( 'TableName', 'TableName'[Store], 'TableName'[City] ) ) = 1 RETURN IF ( IsUnique, 1, RANKX ( CALCULATETABLE ( 'TableName', ALLEXCEPT ( 'TableName', 'TableName'[Store], 'TableName'[City] ) ), [Product], , DESC, DENSE ) - 1 )
@Zubair_Muhammad This is almost accurate. I have a lot values that end up as 2 or more. One goes up to 23. Is it possible to make it a bianary result like a Yes or No?
@Zubair_Muhammad So this is the result i'm getting. When really i need the first instance of a store location to read as 1 and all the other instances to read as 0. Sorry for not explaining that at the top, my bad!
Sorry for late reply. Here is the revised formula
Column = VAR IsUnique = CALCULATE ( COUNTROWS ( 'TableName' ), ALLEXCEPT ( 'TableName', 'TableName'[Store], 'TableName'[City] ) ) = 1 RETURN IF ( IsUnique, 1, VAR myrank = RANKX ( CALCULATETABLE ( 'TableName', ALLEXCEPT ( 'TableName', 'TableName'[Store], 'TableName'[City] ) ), [Product], , ASC, DENSE ) RETURN IF ( myrank = 1, 1, 0 ) )
Infact the formula can be reduced to following. If you need result as Binary, you can chantge the data type to Binary from the Modelling tab>>formatting section. 1 is equivalent to TRUE, 0 is equivalent to FALSE
Column Formula = VAR myrank = RANKX ( CALCULATETABLE ( 'TableName', ALLEXCEPT ( 'TableName', 'TableName'[Store], 'TableName'[City] ) ), [Product], , ASC, DENSE ) RETURN IF ( myrank = 1, 1, 0 )
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |