Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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:
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
Anonymous
Not applicable

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
Not applicable

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 

 

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:
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.
Anonymous
Not applicable

Hi @v-lili6-msft,

 

Your solution works perfectly, Thanxs!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.