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
PBIX_COACH
Helper II
Helper II

Tally for my qtr totals

Hi Team ,

 

I have hit a wall and I need some help.

 

I need to create a measure that captures my quarterly totals but some are aggregated and some are not and are just based on the last entered value for that month. The aggregation is fine but when I don't know how to get the last value to show in the qtr total as I have displayed below.

 

I have a date dimension table connected to the sales table in my model.

 

 

Screenshot 2023-03-22 124205.png

 

any help is greatly appreciated.

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your  datamodel.

Jihwan_Kim_1-1679546242721.png

 

 

Jihwan_Kim_0-1679546230267.png

 

 

Revenue: = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Calendar'[Month number] ), SUM ( Data[Revenue] ),
    ISINSCOPE ( 'Calendar'[Year-Q] ),
        VAR _yearquarter =
            MAX ( 'Calendar'[Year-Q] )
        VAR _quartertable =
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year-Q] = _yearquarter )
        VAR _lastnonblankmonth =
            MAXX (
                FILTER (
                    ADDCOLUMNS ( _quartertable, "@rev", CALCULATE ( SUM ( Data[Revenue] ) ) ),
                    [@rev] <> BLANK ()
                ),
                'Calendar'[Month number]
            )
        RETURN
            CALCULATE (
                SUM ( Data[Revenue] ),
                'Calendar'[Year-Q] = _yearquarter,
                'Calendar'[Month number] = _lastnonblankmonth
            )
)

 

Customoer count: = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Calendar'[Month number] ), DISTINCTCOUNT(Data[Customer]),
    ISINSCOPE ( 'Calendar'[Year-Q] ),
        VAR _yearquarter =
            MAX ( 'Calendar'[Year-Q] )
        VAR _quartertable =
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year-Q] = _yearquarter )
        VAR _lastnonblankmonth =
            MAXX (
                FILTER (
                    ADDCOLUMNS ( _quartertable, "@count", CALCULATE ( DISTINCTCOUNT(Data[Customer]) ) ),
                    [@count] <> BLANK ()
                ),
                'Calendar'[Month number]
            )
        RETURN
            CALCULATE (
                DISTINCTCOUNT(Data[Customer]),
                'Calendar'[Year-Q] = _yearquarter,
                'Calendar'[Month number] = _lastnonblankmonth
            )
)

 

Sales size: = 
SUM( Data[Sales size] )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
PBIX_COACH
Helper II
Helper II

Hi @Jihwan_Kim quick question. I noticed in my Matrix visual that when I drill up to the quarter level the values do not show. For the value I am also using a switch function for the values See the below example. I need to be able to drill up and show the data at the quarter level.

Screenshot 2023-03-30 2227011.png

 

Screenshot 2023-03-30 222110.png

PBIX_COACH
Helper II
Helper II

@Jihwan_Kim  to the rescue again you nailed it. thanks so much.

Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your  datamodel.

Jihwan_Kim_1-1679546242721.png

 

 

Jihwan_Kim_0-1679546230267.png

 

 

Revenue: = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Calendar'[Month number] ), SUM ( Data[Revenue] ),
    ISINSCOPE ( 'Calendar'[Year-Q] ),
        VAR _yearquarter =
            MAX ( 'Calendar'[Year-Q] )
        VAR _quartertable =
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year-Q] = _yearquarter )
        VAR _lastnonblankmonth =
            MAXX (
                FILTER (
                    ADDCOLUMNS ( _quartertable, "@rev", CALCULATE ( SUM ( Data[Revenue] ) ) ),
                    [@rev] <> BLANK ()
                ),
                'Calendar'[Month number]
            )
        RETURN
            CALCULATE (
                SUM ( Data[Revenue] ),
                'Calendar'[Year-Q] = _yearquarter,
                'Calendar'[Month number] = _lastnonblankmonth
            )
)

 

Customoer count: = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Calendar'[Month number] ), DISTINCTCOUNT(Data[Customer]),
    ISINSCOPE ( 'Calendar'[Year-Q] ),
        VAR _yearquarter =
            MAX ( 'Calendar'[Year-Q] )
        VAR _quartertable =
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year-Q] = _yearquarter )
        VAR _lastnonblankmonth =
            MAXX (
                FILTER (
                    ADDCOLUMNS ( _quartertable, "@count", CALCULATE ( DISTINCTCOUNT(Data[Customer]) ) ),
                    [@count] <> BLANK ()
                ),
                'Calendar'[Month number]
            )
        RETURN
            CALCULATE (
                DISTINCTCOUNT(Data[Customer]),
                'Calendar'[Year-Q] = _yearquarter,
                'Calendar'[Month number] = _lastnonblankmonth
            )
)

 

Sales size: = 
SUM( Data[Sales size] )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.

Top Solution Authors