Forum Discussion

mfminor's avatar
mfminor
Helper I
9 years ago
Solved

Help preventing filtering on table visualization

I have a measure want to use to divide the count of specific partnumbers vs the total number of specific units processed for the reporting period. To get the percentage I created the following measur...
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    mfminor

     

    In your scenario, your total count of units processed (formula count) is associcated with each Unit Description, and the ([PN_Used Count]) is associated with each [PN_Used], you shoud have two counts group on different column, the measure should be like:

     

    PN_Used Count vs Formula Count = 
CALCULATE(AviationTable[PN_Used Count],ALLEXCEPT(AviationTable,AviationTable [PN_Used]))
/ 
CALCULATE(aviationtable[Formula Count],ALLEXCEPT(AviationTable,AviationTable[Unit Description]))

    Regards,

     

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

mfminor

 

In your scenario, your total count of units processed (formula count) is associcated with each Unit Description, and the ([PN_Used Count]) is associated with each [PN_Used], you shoud have two counts group on different column, the measure should be like:

 

PN_Used Count vs Formula Count = 
CALCULATE(AviationTable[PN_Used Count],ALLEXCEPT(AviationTable,AviationTable [PN_Used]))
/ 
CALCULATE(aviationtable[Formula Count],ALLEXCEPT(AviationTable,AviationTable[Unit Description]))

Regards,

 

mfminor's avatar
mfminor
Helper I
9 years ago

v-sihou-msft,

 

You appear to have answered my question, but I now have a new "issue".  In my table it appears that I was able to get the percentages I was looking for but my PN-Used Count and Formula Count columns are still filtered. I tried using the measure below to see the full [formula count] unfiltered, but it then displays the total distinct count as shown. I want it to show me the count of distinct SN_Received "filtered" by what Unit Description it is instead. Again I am really new to DAX and Power BI so this is probably really easy.

 

Formula Count Unfiltered = CALCULATE(
	DISTINCTCOUNT(AviationTable[SN Received]),
	ALLEXCEPT(AviationTable,AviationTable[SN Received])
)

 

Below is how the table looks using this measure.

 

I hope I have enough information included. Again, what I would like to see is [Formula Count Unflitered] to actually be "filtered" by [Unit Description]. Or in your language for [Formula Count Unfiltered] to only be associated with [Unit Description]

  • mfminor's avatar
    mfminor
    Helper I
    9 years ago

    Found my problem. Was a really big "DUH" moment. 

     

    Formula Count Unfiltered = CALCULATE(
	DISTINCTCOUNT(AviationTable[SN Received]),
	ALLEXCEPT(AviationTable,AviationTable[Unit Description])
	
)

    Solution to my problem