cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
khana04 Frequent Visitor
Frequent Visitor

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
Community Support Team
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.

 

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

khana04 Frequent Visitor
Frequent Visitor

Re: DAX help

%byScrapCode is calcualted as below

 

 

%byScrapCode = scrapCodeindividualQTY/TotalScrapCodeQTy * 100

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 105 members 1,646 guests