DAX help

[Anonymous]

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 ???

[v-yuta-msft]

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:

1. Calculate [%ByScrapCode] using Power Query instead of DAX in Power BI Desktop, which will make [%ByScrapCode] as a column in your table, if you have any questions about how to calculate [%ByScrapCode] in query editor, please share sample data of your table.

1. Sort table ‘DimTransaction’ by column [%ByScrapCode], then create an index column [ScrapCodeRANK] from 1. You can use M formula below in Edit Queries:

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

[Anonymous]

%byScrapCode is calcualted as below

%byScrapCode = scrapCodeindividualQTY/TotalScrapCodeQTy * 100