Post reply

Help on Aggregation

  • December 15, 2023 at 3:40 pm

    #4346673

    Recently started SSAS. I have a Date Dimension, Day --> Month --> Quarter --> Year and a simple Sales Fact table. I want to calculate the percentage and aggregate only if the percentage is => 75% (please see image) on the DimDate Hierarchy.

    So below (showing Quarter to Year), the Q3 of 60% shouldn't be calculated on the Year aggregation since it is below 75%.

    DS

    TIA

     

     

  • December 16, 2023 at 4:10 pm

    #4347272

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • December 17, 2023 at 6:08 pm

    #4347771

    I want to calculate the percentage and aggregate only if the percentage is => 75%

    You have to use FILTER() for that, because that's the only way to filter a table using a measure, so something like

    FILTER( <expression that returns a table or just a table>, [Percentage]>=.75)

    so the simple version could be

    FILTER (FactTable, [Percentage]>=.75)

    or moving sort of backwards a little

    FILTER( FactTable,

    DIVIDE( [SaleCount], [Inventory])> 0.75)

    yeah, that's a lot of words to say that unless you're doing much more than a simple [column] > k filter, then you need the FILTER() function to do it.

    you'd probably be better posting questions like this on StackOverflow. Not many people here besides Martin Schoombee do much of anything with DAX.

  • December 17, 2023 at 9:27 pm

    #4347846

    I can't help with SSRS but I'm still trying to figure out why anyone would want to remove facts that matter from an aggregate.  This appears to be a "only report the stuff that makes us look good" type of action.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 18, 2023 at 12:27 pm

    This was removed by the editor as SPAM

  • December 18, 2023 at 12:28 pm

    This was removed by the editor as SPAM

  • December 22, 2023 at 4:37 pm

    #4350909

    Thank you. I'll also check the site.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply