Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX help

Scrap_CodeScrap_Total_Qty%ByScrapCodeRunningTotalsScrapCodeRANK
2075818.60%37.21%1
2130818.60%37.21%1
2205716.28%53.49%3
2067613.95%67.44%4
210049.30%76.74%5
211736.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
v-yuta-msft
Community Support
Community Support

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:

 2.PNG

 

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

 1.PNG

Best Regards,

Jimmy Tao

Anonymous
Not applicable

%byScrapCode is calcualted as below

 

 

%byScrapCode = scrapCodeindividualQTY/TotalScrapCodeQTy * 100

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.