cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Micelos
Frequent Visitor

percent of column total

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:

 

1.png

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: percent of column total

hi @Micelos 

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:
6.JPG
 
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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
SKH19 Resolver I
Resolver I

Re: percent of column total

Hi @Micelos 

 

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."

 

Micelos
Frequent Visitor

Re: percent of column total

Hi @SKH19, 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 

 

Community Support
Community Support

Re: percent of column total

hi @Micelos 

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:
6.JPG
 
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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Micelos
Frequent Visitor

Re: percent of column total

Hi @v-lili6-msft,

 

Your solution works perfectly, Thanxs!!!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors