Forum Discussion

lacq's avatar
lacq
Regular Visitor
8 years ago
Solved

DAX: Rollup value within a group

Hi Community,   I've one question, maybe a silly one, but I already wasted some time trying to solve the following requirement...   I've the following very simple star schema model in my PBI file...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi lacq

     

    First add a calculated column in your FactValues

     

    Use this Column in the Visual

     

    CountryName = related(DimCountry[CountryName])

     Now you can use this MEASURE to get the Cumulative

     

    Cumulative =
CALCULATE (
    SUM ( FactValues[Value] ),
    FILTER (
        ALLEXCEPT ( FactValues, FactValues[CountryName] ),
        FactValues[Date] <= SELECTEDVALUE ( FactValues[Date] )
    )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

lacq

 

I think you just need to replace CountryName with CityName in DAX.

lacq's avatar
lacq
Regular Visitor
8 years ago

Hi Zubair_Muhammad,

 

I'm sorry I didn't explain myself well.... when I said that I have a new "group by", City_name, I would like to maintain the Country Name Group... maybe the City_Name was a bad example... let's try with  ProductName, so the same product might be sold in different countries.. and I would like to roll up the value by Country_Name and Product_Name .

 

Thanks again, lacq

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    lacq

     

    In that case, I believe you will just need to extend the

     

    ALLEXCEPT ( FactValues, FactValues[CountryName] )

     to

     

    ALLEXCEPT ( FactValues, FactValues[CountryName], FactValues[ProductName] )

     

     

    • nickchobotar's avatar
      nickchobotar
      Icon for Skilled Sharer rankSkilled Sharer
      8 years ago

      Hello lacq

       

      Just poking in with slightly different approach which does not require bringing in the country name string into the fact table via RELATED()


      = CALCULATE (
    SUM ( FactValues[Value] ),
    FILTER (
        ALL ( FactValues ),
        FactValues[Date] <= MAX ( FactValues[Date] )
            && FactValues[CountryKey] IN VALUES ( DimCountry[CountryKey] )
    )
)


      Zubair_Muhammad  Really liked your SELECTEDVALUE() over the dates

       

       

      Thank, Nick -

  • AndersKa's avatar
    AndersKa
    Regular Visitor
    1 year ago

    Puh. More challenges I'm afraid. 

     

    I am trying to fix a Lead Time Histogram based on the unpivoted data.

     

    When using the old (wrong method) it was fairly simple and here's the result:

    Now of course I have more rows per unique key so I end up with duplicates in a number of bins...

    Do I create a new rolled up table with the Summarize function or is there a clever way of doing this?

     

    Many many thanks in advance.