Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lacq
Regular Visitor

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, 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.

 

daxDoubt.jpg

 

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

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

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.

newRequirements.jpg

 

 

Thanks in advance, lacq

 

 

 

@lacq

 

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


Regards
Zubair

Please try my custom visuals

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

@lacq

 

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

 

ALLEXCEPT ( FactValues, FactValues[CountryName] )

 to

 

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

 

 


Regards
Zubair

Please try my custom visuals

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 -

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.