Forum Discussion

Joe_100's avatar
Joe_100
Helper I
7 years ago

Matrix remove totals(first value) from text

Hi all,

 

I am trying to create a matrix that will have some text values in the columns.

Unfortunetaly Power Bi assumes that i want the first value as subtotal or grand total.

 

I almost got it working by creating a measure:

Text_Measure =

var CurrentItem = SELECTEDVALUE(Projects[KEY])

RETURN
SWITCH(CurrentItem;

CurrentItem;SELECTEDVALUE(Projects[Text]);

blank()

)

 

The only problem i run into now that it will still shows the first value in the subtotals if there is only ONE value.

 

See example below:

(First_text) is the "normal" column, Text_Measure is the measure i have created that is stated here above.

As you can see it only shows a subtotal if there is only one value.

 

Someone has an idea how to remove the subtotal if there is only one value??

 

Many thanks!

Need Help

2 Replies

  • AnthonyTilley's avatar
    AnthonyTilley
    Solution Sage
    7 years ago

    i hope i understood what you where looking for 

     

    use the hasonefilter function

     

    i do not have your source data to replicate your measure but in the example below 

    i have a text colunm that will say Project + project number as text but only in the subrows nothing shows in subtotals or grand totals as these are replaced with blank 

     

    Measure = 

Var o = "PROJECT" & MAX('Table'[PROJECT])
var ret = if(HASONEFILTER('Table'[PROJECT]),o,blank())
return ret

     var o can be replaced with what ever you want to appear in your table 

    var ret then checks if the value has only one filter for the project colunm 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Click the matrix, go to format, then subtotals and turn them off.