Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Scrap_Code | Scrap_Total_Qty | %ByScrapCode | RunningTotals | ScrapCodeRANK |
2075 | 8 | 18.60% | 37.21% | 1 |
2130 | 8 | 18.60% | 37.21% | 1 |
2205 | 7 | 16.28% | 53.49% | 3 |
2067 | 6 | 13.95% | 67.44% | 4 |
2100 | 4 | 9.30% | 76.74% | 5 |
2117 | 3 | 6.98% | 83.72% | 6 |
RunningTotals = CALCULATE( [%ByScrapCode],TOPN([ScrapCodeRANK],ALL(DimTransaction[Scrap_Code]),[%ByScrapCode]))
I am trying to get the RunningTotals Corrected in above Visual to break the tie , so it should be 18.60 then 37.21 and then 53.49 and so on , for some reason it giving me Runningtotals by adding number which have same scrapcoderank, any idea how to fix this ???
Hi khana04,
Firstly, in your measure, are you using RANKX() to calculate the measure [ScrapCodeRANK]? If you are, RANKX() will always return the same ranking for the same value.
In addition. TopN() will take all the rows with same value in a column as one row. So, to achieve your requirement, you should use edit query to create a new ranking column instead of using DAX.
Please refer to steps below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc2xDcAwCATAXZDSWQhMwHgWy/uvYV6KuxTfnJ5nLepdnBqNigb3fGi3UjUpSWhyyKcy/E8D1wE1nn4XBAtvZbLdqiqqhjpP/NoH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Scrap_Code = _t, Scrap_Total_Qty = _t, #"%ByScrapCode" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"%ByScrapCode", Percentage.Type}}),
#"Sorted Rows2" = Table.Buffer(Table.Sort(#"Changed Type",{{"%ByScrapCode", Order.Descending}})),
#"MyRank" = Table.AddIndexColumn(#"Sorted Rows2", "ScrapCodeRANK",1,1)
in
#"MyRank"
The result is like below:
3.Creata measures [RunningTotals] with DAX formula below:
RunningTotals = CALCULATE(SUMX(DimTransaction, [%ByScrapCode]), FILTER(ALL(DimTransaction), DimTransaction[ScrapCodeRANK] <= MAX(DimTransaction[ScrapCodeRANK])))
The final result is like below and you can refer to PBIX file here:
https://www.dropbox.com/s/e2x5l2hj41sl13n/For%20khana04.pbix?dl=0
Best Regards,
Jimmy Tao
%byScrapCode is calcualted as below
%byScrapCode = scrapCodeindividualQTY/TotalScrapCodeQTy * 100
User | Count |
---|---|
15 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
17 | |
11 | |
5 | |
4 | |
3 |