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

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

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?

      • NickolajJessen's avatar
        NickolajJessen
        Solution Sage
        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