Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measure with multiple formats

So, this problem has been slowly killing me for the past week and I was hoping some of you could help me. 

 

I need a matrix where the user selects the columns to display and I have made a working version. However, there is one problem, it relies on one measure and it cannot be multiple formats (percent, number etc.) while being filtered by a different table. Let me show you!

 

Here is the model (please note; the ID table filters multiple tables in the real version, so using this for filtering is more user-friendly).

Here is the measure with formatting (the other measure is identical except for the format())

Calculations with formatting = 
Var Columnsvalue = selectedvalue('Columns'[Columns])
Var V1 = Format(sum(Data[V1]);"percent")
Var V2 = Sum(Data[V2])
Var V3 = sum(Data[V3])

return
Switch(
    Columnsvalue;
    "V1";V1;
    "V2";V2;
    "V3";V3
)

As you can see, it works fine without the formatting (the below matrix is only for illustration and obviously doesn't need to be a percent).

Basically, when formatted it shows all items regardless of ID. This becomes an issue when the list of items grows. 

The above ID comes from the ID table, but the strangest part is that it works fine when filtered from the same table. 

 

Can anyone explain this behavior to me? 

 

Need Help
  • jtownsend21's avatar
    jtownsend21
    7 years ago

    Perfect! thanks for sharing the data Anonymous. Had to do a little work around, but this worked.  Let me know if that works in your production environment. 

    Calculations with formatting = 
Var Columnsvalue = selectedvalue('Columns'[Columns])
Var V1 = IF(ISBLANK(SUM(Data[V1])),BLANK(),Format(sum(Data[V1]),"percent"))
Var V2 = Sum(Data[V2])
Var V3 = sum(Data[V3])

return
Switch(
    Columnsvalue,
    "V1",V1,
    "V2",V2,
    "V3",V3
)

    You can also use the following to fix the 3200% issue and change the formatting to 1 decimal rather than two. I trust you can edit it if you want no decimals, just giving you the options. 

    Var V1 = IF(ISBLANK(SUM(Data[V1])),BLANK(),Format(sum(Data[V1])/100,"#.0%"))

     

9 Replies

  • jtownsend21's avatar
    jtownsend21
    Responsive Resident
    7 years ago

    If I understand correctly, you have two formulas 1.) with formatting, and 2.) without formatting. 

    You posted your version with formatting in your post. Could you share the one without formatting as well? 

     

    As far as the percent showing up as 3200% instead of 32%, the easiest way to fix this is simply to divide by 100 (see below). 

    Var V1 = Format(sum(Data[V1])/100;"percent")
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Here is the measure without formatting;

      Calculations without formatting = 
Var Columnsvalue = selectedvalue('Columns'[Columns])
Var V1 = sum(Data[V1])
Var V2 = Sum(Data[V2])
Var V3 = sum(Data[V3])

return
Switch(
    Columnsvalue;
    "V1";V1;
    "V2";V2;
    "V3";V3
)

      The problem is not that is shows as 3200%, but that it shows the blank rows when filtered. 

      These numbers come from smashing my keyboard, so just pretend it says 32% :-)

      • jtownsend21's avatar
        jtownsend21
        Responsive Resident
        7 years ago

        Anonymous Sorry I miscommunicated. I thought you were asking for help on both issues. 

        To address the extra rows, (I could be wrong on this) I believe what is happening is when you put the formatting in the calculation, it hardcodes and applies to all rows such that it no longer sees it as a blank row, but rather a row with a % sign in it. 

        To avoid this behavior.... Not 100% sure.

        This might be a dumb question, but have you tried to use the formatting in the modelling tab?