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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Shreesefwere
Helper IV
Helper IV

subtotal of column

converting text to values without using sum() or any other aggregation
11m ago

Hi Expert,

 

Have 2 columns in dimension which shows currently in dimension as we did not sum it but wanted to see the subtotal for the same in mdx query

 

i.e.

col1 col2  required column in should show same values without doing sum but should show the sumtotal and max mean

a       40  40

b       60  60

Total        100

6 REPLIES 6
Shreesefwere
Helper IV
Helper IV

suggestion please

v-rzhou-msft
Community Support
Community Support

Hi @Shreesefwere ,

 

I think you want to sum the max col2 per col1 in subtotal.

My Sample:

RicoZhou_1-1676360888928.png

You can try this code to create a measure.

 

Sum of Max col2 = 
SUMX(SUMMARIZE('Table','Table'[col1],"Max col2",MAX('Table'[col2])),[Max col2])

 

Result is as below.

RicoZhou_0-1676360854266.png

 

Best Regards,
Rico Zhou

 

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

i just want subtotal of the col2 without changing the values on row level

for an example : a comes forr 3 times and so it should not sum but only show subtotal

a 10

a 10

a 30

T 40

Hi @Shreesefwere ,

 

I think you can try this code to create a measure.

Subtotal = 
SUMX(DISTINCT('Table'[Col2]),[Col2])

My Sample:

RicoZhou_1-1676453387951.png

Result is as below.

RicoZhou_0-1676453381419.png

 

Best Regards,
Rico Zhou

 

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

 

Shreesefwere
Helper IV
Helper IV

suggestion please

Shreesefwere
Helper IV
Helper IV

suggestion pls

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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