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

Table visual total

i have data like below 

sambhu_0-1612540719557.png

 

when use max in the column and also in measure the total in the table visual showing max value instead of total.

 

please help iam new to dax

 

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

Hi, @Anonymous 

You can try the following Measure.

 

max_original_data =

IF (

    HASONEFILTER ( 'Table'[amount] ),

    MAXA ( 'Table'[amount] ),

    SUMX ( 'Table', 'Table'[amount] )

)

 

The Table visual will looks like this:

v-cazheng-msft_0-1612850670230.png

 

 

Best Regards,

Caiyun Zheng

 

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

View solution in original post

6 REPLIES 6
BA_Pete
Super User
Super User

Hi @Anonymous ,

 

Could you take a look through this thread and mark any answers that have solved your issue as the solution(s) please? This will help others with the same issue to find the answer quicker.

 

If none of these solve the issue for you, can you provide some more details about where they fall down so we can work to get this resolved for you?

 

Thanks,

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




v-cazheng-msft
Community Support
Community Support

Hi, @Anonymous 

You can try the following Measure.

 

max_original_data =

IF (

    HASONEFILTER ( 'Table'[amount] ),

    MAXA ( 'Table'[amount] ),

    SUMX ( 'Table', 'Table'[amount] )

)

 

The Table visual will looks like this:

v-cazheng-msft_0-1612850670230.png

 

 

Best Regards,

Caiyun Zheng

 

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

BA_Pete
Super User
Super User

Hi @Anonymous ,

 

The total row in Power BI is not a sum of the values in a column, it is the measure/calculation in that column calculated with no filters on it, therefore it will just show the MAX of all the unfiltered data, rather than the sum of the MAX values.

 

In order to help you out more, I'll need you to provide the measure/calculation that you want summed, and a sample of your data in table format such as Excel, or an embedded table in your reply (not a picture). I presume your [amount] column is the one that contains the MAX calculation, so providing that calculation would be helpful.

 

Pete

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

@BA_Pete 

 

i need to show distinct rows as values in the table visual and also need total as sum of all the rows in the table visual grand total. it is same type of data iam using, different category item so sales amount column has same values since it belongs to same product.

Hi @Anonymous ,

 

I don't feel your reply above has provided the information required, so I've made some assumptions in order to get this fixed for you, because I'm nice like that 🙂

 

My assumption is as follows:

You want to display the maximum [amount] value for each [year] / [company] combination in the table rows, but you want the total bar so show the SUM of these MAX value rows.

 

My solution based on the above assumed requirements:

 

Use this measure:

_yourMaxSumMeasure = 
VAR maxTable =
SUMMARIZE(
    yourTable,
    yourTable[year],
    yourTable[company],
    "Max Amount", MAX(yourTable[amount])
)
RETURN
SUMX(
    maxTable,
    [Max Amount]
)

 

This gives me the following output based on slightly adjusted sample data:

BA_Pete_0-1612771133036.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Anonymous
Not applicable

Hi @Anonymous 

to get the total amount use the below measure

Total Amount=

CALCULATE(sum('Order Details'[Sales])
 
Where Order details is your table name and Sales is the column name
 
Appreciate you kudos , Press the thums up button !!
 

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.