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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mespiritu
Helper I
Helper I

Why are my measures looking this? How can I have it look normal on a bar chart?

mespiritu_0-1718151745156.png

mespiritu_1-1718151772738.png

 

 

1 ACCEPTED SOLUTION

in PQ select the first three columns and unpivot other columns

11.PNG12.PNG

 

then you just need to drag attribute to x-axis and value to y-axis

13.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

11 REPLIES 11
mespiritu
Helper I
Helper I

The measures I added on the Y axis were from added "Custom Columns" and I used a custom column formula (if then calculation) to get the sum of each month. 

 

could you pls provide some sample data?

if you unpivot the columns ,you only need to create one measure to display all charts.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sure. To give you some context. Each month updating the report, I have to provide a visual of 12 rolling months (I have already figured how to calculate that with my data) the following Columns Monthyear was from adding custom columns by using a custom column formula per Month and date. Goal is to put the sum of each month on a visual chart. 

Sample data screen shot

mespiritu_0-1718219786652.png

 

in PQ select the first three columns and unpivot other columns

11.PNG12.PNG

 

then you just need to drag attribute to x-axis and value to y-axis

13.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi thank you so much for working with me and finally came across a solution. I did everything mentioned above, but only difference is that I created a duplicate table, so that my other data values in my original data table stay the same.  With the duplicated table, I removed all other columns and kept my unpivoted columns only and finally able to visually chart the 12 rolling months. 

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




While trying to apply changes above I'm receiving this error about a client ID that contains a duplicate in the SECTOR table that's consider the primary key table - I double checked and there is no duplicate so I'm a bit confused. 

mespiritu_0-1718642073975.png

 

ryan_mayu
Super User
Super User

it's not a good way to present your data. usually we will unpivot your data in the power query, then all the date will be in a column and all value will be in another column

 

https://prod.support.services.microsoft.com/en-au/office/unpivot-columns-power-query-0f7bad4b-9ea1-4...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




aduguid
Solution Sage
Solution Sage

I'd use a calendar table and join to that in the model view. Then you can can use the month from the calendar in the X-axis of the chart. After that you can just use one measure to calculate the sum.

aduguid_0-1718153808665.png

 

aduguid_0-1718153302930.png

Here's an example of a calendar that I use quite frenquently.

 

 

Calendar = 
VAR _today_date =        TODAY()  //'Properties'[Today Date]
VAR _fiscal_year =       YEAR(EDATE( _today_date, 6))
VAR _fiscal_year_start = DATE ( _fiscal_year - 1, 07, 01)
VAR _fiscal_year_end =   DATE ( _fiscal_year, 06, 30)

VAR _result = 
    ADDCOLUMNS
    (
        //CALENDARAUTO()   //range of dates is calculated automatically based on data in the model
        CALENDAR(_fiscal_year_start, _fiscal_year_end)
        , "Calendar Year Period End",   FORMAT([Date], "yyyy12")
        , "Calendar Year Period Start", FORMAT([Date], "yyyy01")
        , "Calendar Year Period",       FORMAT([Date], "yyyyMM")
        , "Calendar Year Quarter Nbr",  QUARTER([Date])
        , "Calendar Year Quarter",      FORMAT([Date], "\C\Yyyyy \Qq")
        , "Calendar Year Half",         FORMAT([Date], "\F\Yyyyy \H") & ROUNDUP(MONTH([Date]) / 6, 0)
        , "Calendar Year",              YEAR([Date])
        , "Day Name Short",             FORMAT([Date], "DDD")
        , "Day Name",                   FORMAT([Date], "DDDD")
        , "Day Of Week",                WEEKDAY([Date])  
        , "Day",                        DAY([Date])
        , "Fiscal Year Period End",     FORMAT(EDATE([Date], 6), "yyyy12")
        , "Fiscal Year Period Start",   FORMAT(EDATE([Date], 6), "yyyy01")
        , "Fiscal Year Period",         FORMAT(EDATE([Date], 6), "yyyyMM")
        , "Fiscal Year Quarter Nbr",    FORMAT(EDATE([Date], 6), "q")
        , "Fiscal Year Quarter",        FORMAT(EDATE([Date], 6), "\F\Yyyyy \Qq")
        , "Fiscal Year Half",           FORMAT(EDATE([Date], 6), "\F\Yyyyy \H") & ROUNDUP(MONTH(EDATE([Date], 6)) / 6, 0)
        , "Fiscal Year",                YEAR(EDATE([Date], 6))
        , "Is Current FY",              IF(YEAR(EDATE([Date], 6)) = _fiscal_year, 1, 0)
        , "Is Future",                  IF([Date] > _today_date, 1, 0)
        , "Is Past",                    IF([Date] < _today_date, 1, 0)
        , "Month End",                  EOMONTH([Date], 0)
        , "Month Name Short",           FORMAT([Date], "MMM")
        , "Month Name",                 FORMAT([Date], "MMMM")
        , "Month Start",                DATE(YEAR([Date]), MONTH([Date]), 1)
        , "Month",                      MONTH([Date])
        , "Week Ending",                [Date] + 7 - WEEKDAY([Date], 1) // Saturday
        , "Week Starting",              [Date] - WEEKDAY([Date], 1) + 1 // Sunday
        , "Week of Month",              1 + WEEKNUM([Date]) - WEEKNUM( EOMONTH([Date], -1 ) + 1 )
        , "Week of Year",               WEEKNUM([Date])
        , "Week of Fiscal Year",        IF(MONTH([Date]) < 7
                                        , WEEKNUM([Date], 1) + (WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) - 1)
                                        , WEEKNUM([Date], 1) - WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) + 1)
    )

RETURN
_result

 

 

 

jdbuchanan71
Super User
Super User

The chart is leaving room on either side in case you add a category to the X-axis.  You would be better off stacking the data into just a pair of columns (Date and Enrollment), adding a date table and putting month-year on the X-axis.

jdbuchanan71_1-1718153245231.png  jdbuchanan71_2-1718153266294.png

 

jdbuchanan71_0-1718153141654.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors