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
pstanek
Post Patron
Post Patron

Percent of certain group

I have product categories

A B C

 

Subcategories

A.1 ..... A.n

B.1.  B.m

C1.... C.j

 

When I will filter  some category   example A

I need table

A1 8% = percentage A1 of category A

A2 .. 4%

etc.

Total 100%= category A

 

When use quick calculation  percentage of total , it will create

 

 

A1 ... percentage A1 of all categories ABC

Etc.

Total percentage category A of all categories, I means total is not 100%

 

 

How can I create what I mean?

1 ACCEPTED SOLUTION

So,

Writing Precentall = Divide(Table1[Value], calculate(Sum(table1[value]), All(table1)))

In

Precentall = Table1[Value]/calculate(Sum(table1[value]), All(table1)) works here?

Direct Query has limitations:

"

By default, limitations are placed on DAX expressions allowed in measures;

"

If any further help needed, please post back.

Regards

View solution in original post

5 REPLIES 5
pstanek
Post Patron
Post Patron

 

 

 Výstřižek.PNG

I will specify it correctly, I need to calculate matrix for its progress . Month will be column.

I need each month  has 100% , not only summmary 100%

Hi pstanek,

 

Could you please share some details about the data model? Also please share about how the value is calculated.

If you mean to change the total under Matrix Visual, currently there is no available methods. Totals in Visuals are auto-generated by the program, which is based on your data models and the data defined, we could write formula to change the month total to 100%, but this would cause the total column in Matrix visual showing a wrong percentage.

we may consider to submit an idea on this topic to add the Total type in the format pane.

23.PNG

The formula for the Precent calculated column I created is the following:

Precent = Divide(Table1[Value], sumX(filter(table1,Table1[month]=earlier(table1[month])), Table1[Value]))

22.PNG

Precentall is item percentage on all months:

Precentall = Divide(Table1[Value], calculate(Sum(table1[value]), All(table1)))

If you need any further assistance, please feel free to post back.

Regards

 

I don´t if it is result of direct query, but I can´t  find  function divide in power bi desktop anymore.

Plus SUMx cannnot be used within direct query. Earlier desktop doesn´t know too.

 

I used the second way but without divide  so I used "/"

Výstřižek.PNG

So,

Writing Precentall = Divide(Table1[Value], calculate(Sum(table1[value]), All(table1)))

In

Precentall = Table1[Value]/calculate(Sum(table1[value]), All(table1)) works here?

Direct Query has limitations:

"

By default, limitations are placed on DAX expressions allowed in measures;

"

If any further help needed, please post back.

Regards

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.