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

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