Forum Discussion
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 measure
PN_Used Count vs Formula Count = FORMAT(DIVIDE(AviationTable[PN_Used Count], aviationtable[Formula Count],"no result"), "percent")
This should work in theory. The problem comes into play when I put all the information I want presented into a table.
The measure no long looks at all of the specific unit, but the number that have the specific PN_Used. What I want to do is almost matrix like (I tried to use a matrix with the same result). I would like to see a Unit Description followed by the total count of units processed (formula count), then each PN_Used for each Unit with the total number of that specific part number was used. Then I want a percentage calculated by taking the ([PN_Used Count]) that is specific to the [PN_Used] divided by total count of unit description ([Formula Count]). If that makes any sense.
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,
4 Replies
Not entirely following what you are after, but it sounds like a problem that could be fixed with an ALLEXCEPT clause somewhere in your formula to remove certain column contexts from the calculation. In your case, it sounds like an ALLEXCEPT(PN_Used), so remove all context filters except PN_Used. So perhaps something like:
PN_Used Count vs. Formula Count =
VAR myvar = FORMAT(DIVIDE(AviationTable[PN_Used Count], aviationtable[Formula Count],"no result"),"percent") RETURN CALCULATE(myvar,ALLEXCEPT(AviationTable[PN_Used]))Again, not understanding exactly what you are after but something along these lines is probably the answer.
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,
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]
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
