Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

View My Drafts

  • 1.  DAX Measure help - how to total only items filtered

    Posted May 25, 2022 09:20 AM
    Hi,

    I have the following measure which is used to dynamically switch between counts and percentages (using a slicer).  However, when it is displaying percentages and the matrix is filtered with other slicers, the percentage calculation still uses the overall total, not the reduced (filtered) total - thus the overall column doesn't total 100%  I know I need to change the division part (which is wrapped in the FORMAT)  but what do I change it to so that the percentage calculation uses the reduced (filtered) total ?

    Any help would be greatly appreciated.
    Many thanks, Jason

    PercentageViewToggle =
    IF (
    ISFILTERED ( 'SlicerDataViews'[View] ),
    FORMAT( COUNT ( 'Enquiries'[RID] )
                    / CALCULATE ( COUNT ( 'Enquiries'[RID] ), ALLEXCEPT ( 'Enquiries', 'Enquiries'[Enquiry Date] ) ),"0.0%"),
    ( COUNT ( 'Enquiries'[RID] )
    ))


    ------------------------------
    Jason Owens
    MIS Manager
    Batley
    ------------------------------


  • 2.  RE: DAX Measure help - how to total only items filtered

    Top Contributor
    Posted May 26, 2022 07:35 AM
    Hi,
    Try by changing the ALLEXCEPT by ALLSELECTED

    ------------------------------
    If any discussion or answer help, please mark it as "Recommend". If it solve the problem, mark it as "Best answer"
    ------------------------------

    Best regards
    Mehdi HAMMADI

    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------

    Original Message


  • 3.  RE: DAX Measure help - how to total only items filtered

    Posted May 30, 2022 11:56 AM
    Thanks Mehdi.  That has worked great.  The only thing I need to figure out now is how not to display rows with no data.  It only happens when I'm using the percentage measure.  Any ideas?
    Thanks
    Jason

    ------------------------------
    Jason Owens
    MIS Manager
    Batley
    ------------------------------

    Original Message


  • 4.  RE: DAX Measure help - how to total only items filtered

    Posted May 31, 2022 01:59 AM
    Hi,
    You could try some function like hasonevalue or isblank

    ------------------------------
    Dili Timor
    12510
    ------------------------------

    Original Message


  • 5.  RE: DAX Measure help - how to total only items filtered

    Posted May 31, 2022 02:24 PM
    Hi Jason,

    Will filter "is bigger then'   '0' on visual do work?

    Regards,

    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------

    Original Message