DAX: Rollup value within a group

[lacq]

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, and what I want to do is to roll up the value measure per Country Name... I'm able to roll up the value by date, but I need to group the RollupValue by Country Name.

Probably I'm missing something very simple....

Have you ever have to solve a problem like this one? if so, can you share your solution with me?

Many Thanks, Lacq

[Zubair_Muhammad · Accepted solution]

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] )
    )
)

[lacq]

Excellent @Zubair_Muhammad, it works like a charm.....  Well done!!!!

Now imagine that I've another group by, e.g.  City_Name... how can I change the DAX to manage it, i.e.

Thanks in advance, lacq

[Zubair_Muhammad]

@lacq

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

[lacq]

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

[AndersKa]

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.

[Zubair_Muhammad]

@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]

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 -

[Zubair_Muhammad]

@lacq

See the attached sample file