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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
TomasSamu
Frequent Visitor

Problems with continous and categorical x axis (both values and chart styling)

Dear all,

 

would like to get your help in the following. Got a date table (date hierarchy column) connected with sales table. Would like to show the periodical evolution of the total sales amount on customers The continous chart type shows the correct total values for the y axis (somewhat 16M per period within 2020, see below).

 

continous (correct):

 

TomasSamu_0-1594052263104.png

When I change to categorical the overall sum amounts considerably less (somewhat 0.4M per period within 2020, see below).

 

categorical(incorrect):

 

TomasSamu_1-1594052573526.png

 

OK, so I go with the continous type, so far so good. Now my problem is that, when I have a customer with items with no data(see screeenshot of this customer below), I would like to see a gap in the chart - in this particular case stacked area chart having a blank area between August 2020 and May 2021. Adjusting the show items with no data for the axis option does not help(see below.)

 

 

TomasSamu_2-1594052838192.png

 

TomasSamu_3-1594053028362.png

 

To wrap up, switching to categorical x axis type together with the active Show items with no data option DOES solve the visual problem, however I am running into the problem described in the beginning which is that I am getting WRONG values for the totals (see second screenshot in this post). Can you please help me in 1) displaying the correct values (which I am currently getting with continous chart type) as well as displaying correct visual (ideally stacked area chart with blank values for periods with no data, see below)

 

correct:

TomasSamu_4-1594053664705.png

 

Thank you in advance for your help

 

Best,

Tomas

 

 

1 ACCEPTED SOLUTION

Hi @TomasSamu ,

 

As mentioned before, I will attach a sample file.

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
V-lianl-msft
Community Support
Community Support

Hi @TomasSamu ,

 

Create a measure like this :

Total Incident = IF(ISBLANK(SUM(table[MonthlyBaseAmout])),0,SUM(table[MonthlyBaseAmout]))

Drag the measure into the value field.

Use a hierarchy of date columns and retain only the year / month.

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello,

 

thanks for the resnponse @V-lianl-msft , however I am not sure what this measure solves. Tried this out but does not seem to work. Should this solve the problem with data inacurracy I am getting from categorical x axis type?

Maybe my explanation was a little messy so try to clarify once again.

 

So, my first question is why am I getting different values (on y axis) for categorical and continuous x axis types? This happens when my slicers are not active (hence no filter applied). Categorical is indeed inaccurate in my case. The categorical x axis type however allows me to display a chart with blank area for the periods for which there are no values (show items with no data option active). See my last screenshot in my first post for reference. I would like to combine the 2 together - obviously I need correct values (continuous) and good visual (categorical). Opting for one of the x-axis type solvews one of the 2 issues which is insufficient.

 

Hope I clarified.

 

Thanks in advance!

Tomas

Hi @TomasSamu ,

 

As mentioned before, I will attach a sample file.

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.