Forum Discussion

MIRZAMU's avatar
MIRZAMU
Frequent Visitor
4 years ago
Solved

Power BI Data Labels Error

Hi,   In my dashboard, I have selected my bar chart values to show as a % of GT, however when I filter on them by selecting an agents name, the value will go to 100% even though the value I want is...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • NickolajJessen's avatar
    NickolajJessen
    4 years ago

    EDIT:
    MIRZAMU 
    So, it turn out. Using ALL() makes it difficult to use the Filter pane to filter out a person, but is good at crossfiltering. Using Allselected() in stead of ALL() is bad at cross filtering but good at using the filter pane so...

     

    you can create 3 measure

     

    Best Measure ever = 
IF(
    COUNTROWS(ALLSELECTED('Table'[Name])) = 1, 
        [%GT Measure], //If a crossfilter selection is made
         [%GT Measure 2] //If no crossfilter
)

     

     

     

    %GT Measure = 
Var _SelectedWorker = 
    CALCULATE(
        SUM('Table'[PriceCheck])
    )

Var _Total =CALCULATE(
    SUM('Table'[PriceCheck])
   // ,filter(ALL('Table'[Name]), 'Table'[Name] <> "Candy")
   ,ALL('Table'[Name])
)
Return

DIVIDE(_SelectedWorker, _Total)

     

     

     

    %GT Measure 2 = 
Var _SelectedWorker = 
    CALCULATE(
        SUM('Table'[PriceCheck])
    )

Var _Total =CALCULATE(
    SUM('Table'[PriceCheck])
   // ,filter(ALL('Table'[Name]), 'Table'[Name] <> "Candy")
   ,ALLSELECTED('Table'[Name])
)
Return

DIVIDE(_SelectedWorker, _Total)

     

    This set up should be good for both using the filter pane and cross filtering

MIRZAMU's avatar
MIRZAMU
Frequent Visitor
4 years ago

I dont think it is, I just change the 'show as' value to this 

 

NickolajJessen's avatar
NickolajJessen
Solution Sage
4 years ago

Ah yes, that is the problem. 

When you press "Benjamin" on your agent table, your price check visual is filtered to ONLY include data related to benjamin. Meaning you are doing 16/16 = 1 = 100% 

To do 16 / 601 = 2,66%, you need to force power bi to include data from other people, even though you pressed "Benjamin" with the ALL() function

 

  • MIRZAMU's avatar
    MIRZAMU
    Frequent Visitor
    4 years ago

    That looks to have solved the problem, however I am fairly new to PBI and don't understand DAX.

     

    Is there a way you can help me formulate this DAX Code to be specific to my sheet please? As I was trying to replicate it I was finding it tricky

    • NickolajJessen's avatar
      NickolajJessen
      Solution Sage
      4 years ago

      I'm my dummy dataset i just called the table for 'table'
      I see that you called your table for 'data'. You would need to replace 'table' with 'data' for this measure to work.

      On further inspection i'm also seeing that Price Check is not a column by itself, but rather a category in a column, is that correct?

      I'd need some data samples to give a more precise feedback.


  • MIRZAMU's avatar
    MIRZAMU
    Frequent Visitor
    4 years ago

    Price Check is a measure of Order Category you see 

     

    • NickolajJessen's avatar
      NickolajJessen
      Solution Sage
      4 years ago

      It would probably look something like this, if you Name is coming from the 'data' table

      %GT Measure = 
Var _SelectedWorker = [Price Check]

Var _Total =CALCULATE(
    [Price Check]
    ,ALL('Data'[Name])
)
Return
DIVIDE(_SelectedWorker, _Total)
      • MIRZAMU's avatar
        MIRZAMU
        Frequent Visitor
        4 years ago

        Ahhh you're amazing thanks a lot!

         

        Just a last Q, within this DAX Formula, is there a way to exclude a name from the total?

        For example, if I did not want to include Chloe's figures from the total, how could I do that?

        I exclude her in the filters of course but her figures will still affect the total and therefore percentage?