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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
krrish116
Resolver II
Resolver II

Aggregate value based on two columns in Matrix Visual

Hi @amitchandak ,

I have to show some data in a matrix visual

where in rows it contains Group , Division , Branch 

In columns it contains Value , Target.

 

Group     Division     Branch            Value    Target

A               IT             Admin               25         60

                                 Users                  10         50

                                 Workers             25         100

                                               

B             Btech          Admin              35             50

                                 Users                25             100

 

C            CSC             Admin              45          100

                                  Users               35             50

                                  Contracts         55            75

 

For Every Group they had a different values and targets.

Now i have to show aggregate in that matrix visual like

for A  -    Value(25+10+25) / Target(60+50+100)

for  B -  Value(35+25)/ Target(50+100)

etc.,

 

So final result is 

Group     Division     Branch            Value    Target

A               IT             Admin               25         60

                                 Users                  10         50

                                 Workers             25         100

                                Aggregate         total value / total target                      

B             Btech          Admin              35             50

                                 Users                25             100

                                 Aggregate         total value / total target

C            CSC             Admin              45          100

                                  Users               35             50

                                  Contracts         55            75

                                 Aggregate         total value / total target

 

Please help me in solving this.

Thanks,

Krishh

1 ACCEPTED SOLUTION

@krrish116 , Can update your final result with the numbers. Not able to get that yet 

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@krrish116 , that  you should get as subtotal ?

or add new measure

 


divide(sum(Table[Value]), sum(Table[Target]))

 

or

 

calculate(divide(sum(Table[Value]), sum(Table[Target])), filter(allselected(Table), Table[Group] =max(Table[Group])))

Hi @amitchandak ,

I want aggregate for each and every group in the place of subtotal?,

and the value and target are mesures not columns.

Thanks,

Krishhh

 

@krrish116 , do you want to remove the subtotal of division?  Under subtotal, you have an option for Per Row Level Switch on and remove subtotal for division and branch as per need

 

Hi @amitchandak ,

The Value and Target and Subtotal is coming only for Branch.

The Aggregate mesures are not working on matrix visual, if i put that measure on visual then it is showing Blank.

Please help me to sort it out .

I need Aggregate for the bottom of each branch i.e Total Value / Total Target.

Plese let me know, if is not possible due to limitations then i will leave this issue.

Thanks for your support in every issue.

Thanks,

Krishh..

@krrish116 , Can update your final result with the numbers. Not able to get that yet 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.