+ Reply to Thread
Results 1 to 3 of 3

DAX percentage where there is a blank value returns total in p table column

  1. #1
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    594

    DAX percentage where there is a blank value returns total in p table column

    Hello,
    I have a simple pivot table where showing percentage of sales by the product;
    I seem to have what I want but in one of the category's (product id) there is not sales value so the total is put in instead.
    I've solved the problem by using if isfiltered rather than if hasonevalue which worked where there was one.

    Could someone point me in the direction of some information on this as at the moment it's a bit trial
    I've attached the workbook, showing the two examples Showing the problem (product D id 44 ) l and error.

    The two measures are : % of Sales by Product is filtered and % of Sales by Product One Value.

    Richard
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: DAX percentage where there is a blank value returns total in p table column

    Try

    =DIVIDE([Total Revenue]+0,CALCULATE([Total Revenue],ALL(Sales)))
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    594

    Re: DAX percentage where there is a blank value returns total in p table column

    Wow,
    That works, you wouldn't like to elaborate on exactly what adding the zero achieves, does 'hasonevalue' mean exactly that,
    so a null or void is NOT one value? but a zero is?
    Richard
    oh I'm about to go out so if you respond quickly I won't be able to answer until tomorrow.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 11-14-2021, 04:12 PM
  2. Comparing a percentage of a total to the total in pivot table and chart
    By BillDoyle in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-27-2018, 03:56 PM
  3. [SOLVED] Formula to tally total percentage based on whether or not certain fields are blank
    By Mvaldesi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-29-2016, 11:12 AM
  4. [SOLVED] A Second Column Which Shows Percentage Of Total In The Pivot Table
    By zanshin777 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-23-2015, 08:17 AM
  5. [SOLVED] I want to remove the percentage of row total in the pivot table
    By Shellybelly in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-08-2014, 06:51 AM
  6. Replies: 0
    Last Post: 10-15-2012, 06:09 PM
  7. Pivot Table Percentage of Grand Total Column
    By Jacob in forum Excel General
    Replies: 6
    Last Post: 03-08-2006, 01:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1