Forum Discussion

NameTaken's avatar
NameTaken
Frequent Visitor
10 months ago
Solved

Reference Label as a percent

Hello, 

 

I have Customer Groups and then Individual Customer as slicers in my report.
I can currently take the entire customer portfolio, say its 100, and the amount of a Customer Group (call it Group A) say they have 10 purchases. I show on my visual the Group A's 10 on the top card with a reference label of purchase % (10%), then on a card beneath it I show the portfolio's 100. This is working fine.

 

This is the formula I use for my reference label:  

Purchase Share =
DIVIDE(
    [PurchaseCount_Selected],
    [PurchaseCount_Total]
)
 
Here is PurchaseCount_Selected
 
PurchaseCount_Selected =
CALCULATE(
    DISTINCTCOUNT('Volume'[Application_Id]),
    SWITCH(
        TRUE(),
        HASONEVALUE('Volume'[CustomerGroupName]),
            'Volume'[CustomerGroupName] = SELECTEDVALUE('Volume'[CustomerGroupName]),
        HASONEVALUE('Volume'[CustomerName]),
            'Volume'[CustomerName] = SELECTEDVALUE('Volume'[CustomerName])
    )
)

 

Here is PurchaseCount_Total: 

 

PurchaseCount_Total =
CALCULATE(
    DISTINCTCOUNT('Volume'[Application_Id]),
    FILTER(
        ALL('Volume'),
        'Volume'[CreatedDate] >= MIN('Volume'[CreatedDate]) &&
        'Volume'[CreatedDate] <= MAX('Volume'[CreatedDate])
    )
)

 

Again, this is functioning fine. Now the issue is below:

On another tab, that focuses on individual customers, when I try to do the same thing comparing Group A with one of its individual customers (call it A1.0) the reference label does not work. I do have different calculations for it which have gone through many iterations with coworkers as well as Co Pilot. Hoping someone has a fix. It seems to me like customer is fine but its the Group that defaults to the same number as the customer and the reference label will correctly show 100% at that point, but obviously that is not what we are looking to show. I essentially want to show the % of purchases that are from A1.0 vs the entire amount of A. So if Customer A1.0 is credited with 4 purchases of the 10, I want to show 40% in my reference label. 

PurchaseCount_Percent_Selected_Customer =
DIVIDE(
    [PurchaseCount_Selected_Customer],
    [PurchaseCount (Group)],
    0
)
 
Here is PurchaseCount_Selected_Customer
 
PurchaseCount_Selected_Customer =
CALCULATE(
    DISTINCTCOUNT('Volume'[Application_id])
)
 
Here is PurchaseCount (Group):
PurchaseCount (Group) =
CALCULATE (
    DISTINCTCOUNT ( 'Volume'[Application_id] ),
    REMOVEFILTERS ( 'Volume'[CustomerName] )  
)


I am having the CustomerName slicer not interact with the bottom card so that shows the amount for the entire group. 
I have tried to keep it closer to the logic in PurchaseCount_Total but not sure how to treat the All('Volume') in regards to needing a filter on the Customer Group. 

Any help, ideas or fixes would be greatly appreciated. 

Need Help
  • v-hjannapu's avatar
    v-hjannapu
    9 months ago

    Hi NameTaken,

    I have reproduced your requirement and implemented the solution. I have attached the PBIX file for your review so you can see the updates. Let me know if you need any changes or further improvements.


    Regards,
    Community Support Team.

     

6 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    10 months ago

    Hi NameTaken,

     

    Firslty let's create a measure which reoves filters and counts total applications

     

    PurchaseCount (Group) =
    CALCULATE(
    DISTINCTCOUNT('Volume'[Application_id]),
    REMOVEFILTERS('Volume'[CustomerName]),
    KEEPFILTERS(VALUES('Volume'[CustomerGroupName]))
    )

    Now create final % measure

     

    PurchaseCount_Percent_Selected_Customer =
    DIVIDE(
    [PurchaseCount_Selected_Customer],
    [PurchaseCount (Group)],
    0
    )🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

  • v-hjannapu's avatar
    v-hjannapu
    Community Support
    10 months ago

    Hi NameTaken,

    I would also take a moment to thank grazitti_sapna  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
     

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

    Regards,
    Community Support Team.

    • v-hjannapu's avatar
      v-hjannapu
      Community Support
      10 months ago

      Hi NameTaken,
      I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


      Regards,
      Community Support team.

      • NameTaken's avatar
        NameTaken
        Frequent Visitor
        9 months ago

        My apologies for not being able to get back to this sooner. 

        The solution seems to not work with the Created Date Slicer like the Purchase Count Total measure. 

        When using the Created Date the answer was no longer correct so I think I need to add something to keep that filter.
        However when I added: 

        KEEPFILTERS(VALUES('Volume'[CustomerGroupName])),
            'Volume'[CreatedDate] >= MIN('Volume'[CreatedDate]),
            'Volume'[CreatedDate] <= MAX('Volume'[CreatedDate])
        )
         
        the calculation no longer produces the correct answer at all. I need the reference label to respect the Created Date slicer like everything else on the page does.