cancel
Showing results for
Did you mean:
Frequent Visitor

## DAX help

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

2 REPLIES 2
Community Support Team

## Re: DAX help

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.

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}})),

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

Frequent Visitor

## Re: DAX help

%byScrapCode is calcualted as below

%byScrapCode = scrapCodeindividualQTY/TotalScrapCodeQTy * 100

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 105 members 1,646 guests
Recent signins: