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
DogManX
Frequent Visitor

Adjust Calculated Column based on filter applied to another table.

I have a date table (Sales_Totals) where I am calculating the cumulative sales for each day in the quarter pulling the sales numbers from the Sales_Info table based on the Sale Date. So it pulls the cumulative sales for April 1, April 2, etc.

 

I was wondering if there is a way to setup a slicer so that when I filter for the region of the sale (say East, West, North, South) on the Sales_Info table it will impact the values of the Sales_Total table so that it only shows the cumulative values for that region.

 

 

1 ACCEPTED SOLUTION

Hi @DogManX,

 

The measures are based on context so depending on the information you have on your x-axis you are abble to have several points, If you have quarte only it will give one point, months it will give 3 points days it will give 90 points.

 

Untitled.png

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
MFelix
Super User
Super User

Hi @DogManX,

 

To what I can understand you are making the cumulative sales based on a Sales table that has all the information.

 

Why instead of making a table that pulls the cumulative values, you don't replace the full table by a measure? If you want Quarter to Date you should do something like this:

 

Cumulative QTD Sales = TOTALQTD(SUM(Sales_Info[Sales]); Date_Table[Date])

I'm assuming you also have a dates table if not replace the Date_Table part by the Sales Date.

 

If you then add the dates to your visuals and this measure it will give you QTD amounts, there is also TOTALMTD and TOTALYTD formulas.

 

Then since measures are calculated in context if you add the slicer for the region the measure will be filter out.

 

If you wan to have the table for cumulative values that table needs to have also the region of sale in order for you to be abble to filter out.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix - thank you so much for the response.

 

I made a table rather than a measure as I wanted to show cumulative sales compared to the previous year. So I have an area graph that shows this year's sales throughout the quarter compared to the previous year. This amount is being used to graphically visualize if we are on pace to meet our various numbers.

Hi @DogManX,

 

In this case you need to also add another measure for the previou year calculation something like:

 

Cumulative PY = TOTALQTD ( SUM ( Sales_Info[Sales] ); SAMEPERIODLASTYEAR ( Date_Table[Date] ) )

the both measures will be filter by context.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



But will I be able to have a line graph going through the course of the quarter using a measure? I thought I needed a column calculation for it to be more than a single data point?

Hi @DogManX,

 

The measures are based on context so depending on the information you have on your x-axis you are abble to have several points, If you have quarte only it will give one point, months it will give 3 points days it will give 90 points.

 

Untitled.png

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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.