Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

KPI Formatting

I have a page of KPIs for sales by each channel. I have created a dynamic switcher that allows me to switch between total $ amount and percentage of total. However, whenever I try to format these measure it returns blank.

 

For example:

 

Switcher = SWITCH([Selected Measure],1,FORMAT([Direct Mail $],"$000,000.00"),2,CALCULATE(SUM([Total Sales]), [channel] = "Direct Mail"))/FORMAT(SUM('[Total Sales]),"0.00%")

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Anonymous,

     

    I tried it like below successfully. You can give it a try.

    Switcher =
SWITCH (
    [Selected Measure],
    1, FORMAT ( [Direct Mail $], "$000,000.00" ),
    2, FORMAT (
        CALCULATE ( SUM ( [Total Sales] ), [channel] = "Direct Mail" )
            / SUM ( [Total Sales] ),
        "0.00%"
    )
)

    switch

     

    Best Regards,

    Dale

3 Replies

  • RMDNA's avatar
    RMDNA
    Solution Sage
    8 years ago

    Hi Anonymous,

     

    I would set your calculations inside the measure using VAR and then just call them in SWITCH.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      That still does not work. It seems that using the FORMAT() function is not allowed in a KPI

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    I tried it like below successfully. You can give it a try.

    Switcher =
SWITCH (
    [Selected Measure],
    1, FORMAT ( [Direct Mail $], "$000,000.00" ),
    2, FORMAT (
        CALCULATE ( SUM ( [Total Sales] ), [channel] = "Direct Mail" )
            / SUM ( [Total Sales] ),
        "0.00%"
    )
)

    switch

     

    Best Regards,

    Dale