Forum Discussion

MuppetyMe's avatar
MuppetyMe
Helper I
1 year ago
Solved

Slicer Help - Categorizations

Looking for assistance creating a slicer for a set of data, but I'm not even sure it's possible. Below is my sample set. 

 

I want my slicer to have two options: "Pipeline" and "Revenue". This slicer is intended to filter a stacked bar chart where X axis = Month, Y axis = Amount, Legend = Status. 

 

The catch: all rows are Pipeline, but only rows with "Won" in the Status column are Revenue.

 

Initially, I created a new column:  

Pipeline or Revenue Text = IF(Table[Status] = "Won", "Revenue", "Pipeline")
... but I need the Won Amount to be included in Pipeline too so this doesn't work. 

 

I feel like I'm overlooking something super obvious (apologies in advance if that's the case). I understand that making Status a slicer would work, but that's not the direction we need to go. Anyone have thoughts on how to accomplish this? 

 

Table

DateIDStatus Amount 
1/14/202512172Lost$24,833.12
1/21/202512173Lost$2,174.00
2/1/202512174Won$35,488.14
2/3/202512175In Process$12,446.85
2/17/202512176Won$24,688.10
2/22/202512177Won$1,354.00
3/14/202512178In Process$841.68
3/22/202512179Lost$54,887.25
3/23/202512180In Process$899.16
4/5/202512181In Process$35,748.00
4/17/202512182Lost$22,488.30
4/25/202512183Won$5,168.00
5/1/202512184In Process$4,721.10
5/12/202512185In Process$31,864.57
5/20/202512186Won$23,458.00
5/25/202512187Won$2,435.45

 

For illustration purposes, here's the gist of what I'm looking for: 

 

Need Help
  • MFelix's avatar
    MFelix
    1 year ago

    Hi MuppetyMe ,

     

    Create a disconnected table with the two values:

    Pipeline

    Revenue

     

    Then add the following measures:

    Total = SUM('Table'[ Amount ])


total Values = 

SWITCH(
    TRUE(),
         SELECTEDVALUE('Type Selection'[Type]) =  "Pipeline"  , [Total],
         SELECTEDVALUE('Table'[Status]) ="Won" && SELECTEDVALUE('Type Selection'[Type]) = "Revenue", CALCULATE([Total], 'Table'[Status] = "Won"))

     

     

    See PBIX attached.

     

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    Hi MuppetyMe ,

     

    Create a disconnected table with the two values:

    Pipeline

    Revenue

     

    Then add the following measures:

    Total = SUM('Table'[ Amount ])


total Values = 

SWITCH(
    TRUE(),
         SELECTEDVALUE('Type Selection'[Type]) =  "Pipeline"  , [Total],
         SELECTEDVALUE('Table'[Status]) ="Won" && SELECTEDVALUE('Type Selection'[Type]) = "Revenue", CALCULATE([Total], 'Table'[Status] = "Won"))

     

     

    See PBIX attached.

     

    • MuppetyMe's avatar
      MuppetyMe
      Helper I
      1 year ago

      Thank you MFelix and Ashish_Mathur! Both of your suggestions (almost the same, but slightly different) solved my issue. Appreciate your time and help! 

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    Hi MuppetyMe,

    Thank you Ashish_Mathur amd MFelix for your response and for providing the attached file.
    You may refer to the answers included, and I am also sharing the PBI xfile for your reference. Kindly review it at your convenience.

     

    I hope this helps resolve your issue. If you have any questions or need further clarification, I’m happy to help.

    If you found this post useful, kindly give it a ‘Kudos’ and mark it as a solution so others facing similar challenges can find it more easily.

     

    Thanks for being part of the Microsoft Community!