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
PBILP
Regular Visitor

Create a area graph with two cumulative value with two x axis

Hi Power Community.

 

I am trying to create a area graph with two cumulative value on it, both based on a hour value.

 

Here is a sample of my date :

theroerical timereal timeQuantity
14:0014:3710
15:0015:055
16:0015:1025

I am using two measures to calculate my cumulative values, one for each hour :

 

 

CALCULATE(
	SUM('QUANTITES Arrivée'[VRAC]),
	FILTER(
		ALLSELECTED('Quantity'[theroerical time]),
		ISONORAFTER('Quantity'[theroerical time], MAX('Quantity'[theroerical time]), DESC)
	)
)

 

 

For the other measure, replace reference to theroerical time to real time.

 

My issue is that i can't find a way to display my two measure in the same graph because it can't conciliate my two hours column on the same x axe.

I manage to go that far. One measure is correctly cumulated, the other one is not (because X axis doesn't correspond, despite being both a hour value)

My expected result would be to have both area cumulated.

PBILP_0-1657184611626.png

Is there a solution to allow both of my measure to be cumulated here ? My easiest take would be to concialiate the x axis but i failed to do it for the moment.

 

Thanks.

3 REPLIES 3
PBILP
Regular Visitor

Thank for your quick answer. I have read and tried your instructions

 

I have created the TIMETABLE table and then made two relation :

  • active relation Real Time with Time column from the new Timetable
  • inactive relation Theroerical Time with Time column from the new Timetable

After that, I have added the USERELATIONSHIP in my cumulative sum measure (the other measure with active relation remain the same)  

CALCULATE(
	SUM('QUANTITES Arrivée'[VRAC]),
	FILTER(
		ALLSELECTED('QUANTITES Arrivée'[Arrivéethéorique]),
		ISONORAFTER('QUANTITES Arrivée'[Arrivéethéorique], MAX('QUANTITES Arrivée'[Arrivéethéorique]), DESC)
	),USERELATIONSHIP('QUANTITES Arrivée'[Arrivéethéorique],'TimeTable'[Time])
)

Despite this, the Theroerical Time still doesn't cumulate. Did I miss something ?

 

PBILP_0-1657189633784.png

 

 

amitchandak
Super User
Super User

@PBILP , for that you have to create a timetable. Join with both time columns and one join will be inactive, which you have to activate using userelationship

https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

 

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

Hi again @amitchandak,

 

I tried again to use the document you provide me. I have include USERELATIONSHIP on both of my measures. Like this, as a first filter of my CALCULATE operation :

CALCULATE(
	SUM('QUANTITES Arrivée'[VRAC]),
        USERELATIONSHIP('QUANTITES Arrivée'[Arrivéethéorique],TimeTable[Time]),
	FILTER(
		ALLSELECTED('QUANTITES Arrivée'[Arrivéethéorique]),
		ISONORAFTER('QUANTITES Arrivée'[Arrivéethéorique], MAX('QUANTITES Arrivée'[Arrivéethéorique]), DESC)
	)
)

Then i use the TIMETABLE column Time in my area graph for the X axis.

 

This time, both of my value appears with the same axis. However, the value are not cumulated anymore as you can see in the picture below.

Do I need to adjust my measure or my graph do achieve to show my expected cumulated value on the same graph ?

PBILP_0-1657546111763.png

 

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.