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
ElliotP
Post Prodigy
Post Prodigy

Sunburst Measures as Values not appearing

Morning;

 

I'm trying to use the Sunburst Visual to show the last week and the week before that's sales for the section from which they were sold as well as the day of the week they were sold in.

 

When I use the column value it appears; but shows all of the data.

When I use my past week measure as the Value; it doesn't show anything and I'm not sure why?

 

P1WNetSales = 
VAR CurrentWeekNum =
    MAX ( 'ExtendedCalendar'[WeekNum] ) - 1
VAR CurrentYear =
    MAX ( 'ExtendedCalendar'[Year] )
RETURN
    (
        CALCULATE (
            SUM ( 'itemdetailsdogfood$'[Net Sales] ),
            FILTER (
                ALL ( 'ExtendedCalendar' ),
                ExtendedCalendar[Year] = CurrentYear
                    && 'ExtendedCalendar'[WeekNum]
                    = CurrentWeekNum - 0
            )
        )
)

18d0808c96bd6faed82ab1471fbf3dbb.png

 

Do I need to make it a column instead? I've tried copying the code and nothing appears in the column.

1 ACCEPTED SOLUTION

Hi @ElliotP,

 

After looking into your shared pbix file, I found that the variable "CurrentWeekNum" should be defined as below in order to get the max week of current year, or it will return the max week of all years(not only the current year).Smiley Happy

VAR CurrentWeekNum =
    CALCULATE (
        MAX ( 'ExtendedCalendar'[WeekNum] ) - 2,
        FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear )
    )

 And following is the formula to create the measure. And it can be shown on the Card visual or on the Chart with a Date column.

 

P2WNetSales =
VAR CurrentYear =
    MAX ( 'ExtendedCalendar'[Year] )
VAR CurrentWeekNum =
    CALCULATE (
        MAX ( 'ExtendedCalendar'[WeekNum] ) - 2,
        FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear )
    )
RETURN
    (
        CALCULATE (
            SUM ( 'itemdetailsdogfood$'[Net Sales] ),
            FILTER (
                ALL ( 'ExtendedCalendar' ),
                ExtendedCalendar[Year] = CurrentYear
                    && 'ExtendedCalendar'[WeekNum]
                    = CurrentWeekNum - 0
            )
        )
    )

 

Regards

View solution in original post

16 REPLIES 16
ElliotP
Post Prodigy
Post Prodigy

 

 

bbfb921436c7adeb8fb8fd299fdbb082.png

 

 When i just put this on a card it comes up as blank.

 

When i put it on a KPI with the datekey or the weeknumber on the trend axis it shows a figure. Hmm.

 

 

 

In a test document with the data imported from an excel file, if the relationship is set to one to one between date's the values work.

 

In my working document, the relationship is many to one. When I try to change it to one to one it tells me the cardinality isn't valid and I'm unable to change it to one to one. The data is imported from azure sql server.

 

Thoughts?

 

 

I have tried putting the data into blob storage & table storage; no luck. None of them will seem to be accepted as a one to one relationship, citing the cardinality issue.

Thoughts anyone? I'm completly stumped.

Hi @ElliotP,

 

The logic of your fomula seems all right. And I just tested with a formula similar like yours on my side, it works all fine.

 

Could you share a sample pbix file which can reproduce the issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.Smiley Happy

 

Regards

Evening, thanks for the response. I couldn't find how to attached it to this response so I uploaded it here: Link

 

On p1-1 on the right hand side has the sunburst. To the left of it is a bar chart that sort of demonstrates exactly the issue; it simply isn't showing the value with a date.

 

On p2-1 there is a stacked bar chart on the left and a 100% stacked chart which really highlight the issue of simply not displaying the values.

 

I feel my relationships are alright, but I'm completly lost as to why they aren't showing up.

Hi @ElliotP,

 

After looking into your shared pbix file, I found that the variable "CurrentWeekNum" should be defined as below in order to get the max week of current year, or it will return the max week of all years(not only the current year).Smiley Happy

VAR CurrentWeekNum =
    CALCULATE (
        MAX ( 'ExtendedCalendar'[WeekNum] ) - 2,
        FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear )
    )

 And following is the formula to create the measure. And it can be shown on the Card visual or on the Chart with a Date column.

 

P2WNetSales =
VAR CurrentYear =
    MAX ( 'ExtendedCalendar'[Year] )
VAR CurrentWeekNum =
    CALCULATE (
        MAX ( 'ExtendedCalendar'[WeekNum] ) - 2,
        FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear )
    )
RETURN
    (
        CALCULATE (
            SUM ( 'itemdetailsdogfood$'[Net Sales] ),
            FILTER (
                ALL ( 'ExtendedCalendar' ),
                ExtendedCalendar[Year] = CurrentYear
                    && 'ExtendedCalendar'[WeekNum]
                    = CurrentWeekNum - 0
            )
        )
    )

 

Regards

Afternoon,

 

Thank you so much. You have no idea how much time I've spent working on a solution to this and I was at a point of being completly out of ideas. Matt Allington was a tremendous help as well. Thank you so much, that's absolutely amazing.

I'm trying to do this graph, but this keeps happening;

 

 

Here's the code:

 

P2WNetSalesKitchen = 
VAR CurrentYear =
    MAX ( 'ExtendedCalendar'[Year] )
VAR CurrentWeekNum =
    CALCULATE (
        MAX ( 'ExtendedCalendar'[WeekNum] ) - 2, 'itemdetailsdogfood$'[Sections1]="Kitchen",
        FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear )
    )
RETURN
    (
        CALCULATE (
            SUM ( 'itemdetailsdogfood$'[Net Sales] ),
            FILTER (
                ALL ( 'ExtendedCalendar' ),
                ExtendedCalendar[Year] = CurrentYear
                    && 'ExtendedCalendar'[WeekNum]
                    = CurrentWeekNum - 0
            )
        )
    )

I've tried placing the 'itemdetailsdogfood$'[Sections1]="Kitchen" after the Sum function, after the filter function; yet I can't seem to display the values just for the past two weeks. Thoughts on how I would do that? The dates should only be show the individual daily values. I'd like to be able to show each different section "Drinks" and "Kitchen". I don't feel I can filter that and have it appear as two seperate lines.

 

I've tried SUMX and LOOKUPVALUE;

P2WNetSalesKitchen = 
VAR CurrentYear =
    MAX ( 'ExtendedCalendar'[Year] )
VAR CurrentWeekNum =
    CALCULATE (
        MAX ( 'ExtendedCalendar'[WeekNum] ) - 1, 
        FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear )
    )
RETURN
    (
        CALCULATE (
            LOOKUPVALUE('itemdetailsdogfood$'[Net Sales], 'itemdetailsdogfood$'[Sections1], "Kitchen"),
            FILTER (
                ALL ( 'ExtendedCalendar' ),
                ExtendedCalendar[Year] = CurrentYear
                    && 'ExtendedCalendar'[WeekNum]
                    = CurrentWeekNum - 0
            )
        )
    )

I feel it's an issue with the sum returning the sum for that week number as opposed to summing it daily. Maybe I should create a daily VAR; hmmm

When I try and put a visual level filter on it by Section1 for Kitchen for example; it just returns (blank); hmm.

Picture: https://gyazo.com/1431c0031d5df5e666aab60a230c4d9f

 

I'm trying to show the daily sum of net sales for the past two weeks by the Section1: Kitchen.So each day should be the sum'd value for each section and should move up and down with the data as opposed to just a flat line for every day available.

 

I feel I will need to [p1wnetsales]+[p2wnetsales]; but at the moment I feel i'm missing its properly evaluating the time function (due to showing all dates, not just the last two weeks) as well as it's the same value for the entire past two weeks).

 

SUMX doesn't seem to help.

 

Right; I feel confident my filtering is sound:

 

P2WNetSales2 = VAR CurrentYear =
    MAX ( 'ExtendedCalendar'[Year] )
VAR CurrentWeekNum =
    CALCULATE (
        MAX ( 'ExtendedCalendar'[WeekNum] ) - 1,
        FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear )
    )
RETURN
    (
        CALCULATE (
            Sum('itemdetailsdogfood$'[Net Sales]), 'itemdetailsdogfood$'[Sections1]="Kitchen", 
            FILTER (
                ALL ( 'ExtendedCalendar' ),
                ExtendedCalendar[Year] = CurrentYear
                    && 'ExtendedCalendar'[WeekNum]
                    = CurrentWeekNum - 0
            )
        )
    )

 

, FILTER('itemdetailsdogfood$', 'itemdetailsdogfood$'[Sections1] = "Drink")

I've added in this after the first filter and it's working.

 

Now it's just a matter of being able to show the past two weeks of data. I've tried simply summing the measures but that produces one large value instead of a few incremental values.

 

It might be working; let me see how it travels.

https://gyazo.com/62b5433e22cff7663035d110607cb8bb

 

It seems to still be showing the summed total value as if it's everyday. I feel it's a matter of how the table is being filtered. I do want to keep those time filters (in the code).

Any ideas? I'm out of ideas as to why it's showing the same value for all dates. I feel it's a filter issue but I'm not sure how to solve it.

 

I've attached pbix; on sheet p2-2.

 

My pbix

 

Ideas; niether work; but I feel it's my filtering of the table as it's simply summing the values for the week.

 

P0WNetSales1 = VAR CurrentYear =
    MAX ( 'ExtendedCalendar'[Year] )
VAR CurrentWeekNum =
    CALCULATE (
        MAX ( 'ExtendedCalendar'[WeekNum] ) - 1,
        FILTER ( ALL ( 'ExtendedCalendar' ), ExtendedCalendar[Year] = CurrentYear )
    )
RETURN
    (
        CALCULATE (
            SUMX('itemdetailsdogfood$',SUM('itemdetailsdogfood$'[Net Sales])), 
            FILTER (
                ALL ( 'ExtendedCalendar' ),
                ExtendedCalendar[Year] = CurrentYear
                    && 'ExtendedCalendar'[WeekNum]
                    = CurrentWeekNum - 0
            ), FILTER(ALL('ExtendedCalendar'), 'ExtendedCalendar'[DateKey] <= 'ExtendedCalendar'[DateKey])
        )
    )

Even without the second FILTER, the sumx isn't evaluating the table row by row for the filtered number of rows.

Thoughts?

Instead of it showing the weekly sum and then expressing it as the same value for each day; I was hoping for it to sum the daily amounts so it would show the day to day change as opposed to simply showing the sum of the week as the value for each day of the week.

The link below is an excel file with a demonstration of what I'd like to achieve in PowerBI.

http://www.filedropper.com/demonstration

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.