CODEWITHSUNDEEP
data-visualizationpowerbidax
kartik
kartik

Reputation: 168

Power BI - control the drill up behaviour in matrix

I am having data such that multiple currencies are present in single columnenter image description here

As one can see above in the Vested Value column , Now I needed subtotals currency wise but since I am appending the currency symbols alongside values , therefore the datatype of columns is text , so cannot use automatic subtotals. So , I precalculated the subtotal and inserted it as a row inside the table as you can see the "SUBtotal-YEN" row . but since these are text formats , so when I drill up , it gives wrong answers. Thats why I need to cutomise the drill behaviour.Any suggestions on how to go about this?

Upvotes: 0

Views: 912

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

I would recommend creating an explicit measure instead of using the automatic implicit measure. If your VestedValue column is numeric, then you can use the following measure to get the subtotals working as well. (The currency formatting is done after the summing happens.)

VestedMeasure = SWITCH(SELECTEDVALUE(TableName[Home Currency]),
    "USD",FORMAT(SUM(TableName[VestedValue]),"$#,##0"),
    "YEN",FORMAT(SUM(TableName[VestedValue]),"¥#,##0"))

If that column is not numeric, then create a calculated column that is and use that in the measure instead. The following formula strips off the currency symbol and converts to a numeric value:

VestedNumeric = VALUE(RIGHT(TableName[VestedValue], LEN(TableName[VestedValue])-1))

Upvotes: 1

Related Questions