percent of column total

[Anonymous]

Hi everyone,

I have a problem with Power BI not displaying values correctly when they are calculated as percentage of grand total.

The dataset has an ID colum with four values (A, B, C and D), there is also three more columns: year, month and type. This three column are used as slicers.

I want to calculate the number of values for every id, for this i have a matrix:

The matrix shows the number of values for every id, after apply the slicers, the matrix also shows the percent of the column, this was calculate using:

percent = count(Test[ID]) / CALCULATE(count(test[id]);all(Test[ID])).

The problem i have is when use "Id" as slicer and choose for example "A" the number stays the same but the perncet change.

How do I fix this?

Thanks.

[Anonymous]

Hi @Anonymous 

In your formula, instead of using ALL use ALLSELECTED

Let me know if that works.

---
Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."

[Anonymous]

Hi @Anonymous, thanx for your reply.

I tried with ALLSELECTED and didn´t work, i also tried with the formula:

percent = count(Test[ID]) / CALCULATE( COUNT(Accidentes[HORARIO]); ALLEXCEPT(Test; Test[year]; Test[month]; Test[Type])) 

The values of the percent still change.

Test.pbix 

[v-lili6-msft · Accepted solution]

hi @Anonymous 

Basically, they all in one table, preserving filter context on [ID] indirectly preserves filter context on [MONTH] and also [YEAR]/[TYPE], which is not what you want.

So adjust the model as below:

Step1:

Create separate dim ID table and create a relatinship with basic data table

and then use it in table visual and slicer

Step2:

Then adjust the formula as below:

percent = count(Test[ID]) / CALCULATE(count(test[id]),all('Dim ID'[ID]))

Measure = CALCULATE(COUNTA(Test[ID]),ALL('Dim ID'[ID]))

Result:

and here is sample pbix file, please try it.

By the way, for dim fields you'd better use a dim table and create  the relationship with fact table.

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft,

Your solution works perfectly, Thanxs!!!