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
Anonymous
Not applicable

Combining two measures in PowerPivot for use in graphs / pivot tables

Hi everyone,

 

I am looking to combine two measures based on the year so that I can show them as one line in a graph and as a single measure in a pivot table.

 

One measure calculates something for all of 2019 and 2020, then I have another measure calcualting the same thing but from different sources for 2021 onwards. Currently I have to drag the two measures into graphs seperately and have them in different pivot tables with different filters to achieve the correct result. 

 

Is there a way to say if year is 2019/2020, use Measure A, if 2021 onwards, use measure B, so that once combined it would funcion like a single measure pulling the correct data based on the year?

 

I am using PowerPivot not PowerBI so cant create addtional tables, so ideally it would be a DAX measure.

 

Hopefully quite simple but can't seem to find a solution.

 

Thanks all

 

Alex

4 REPLIES 4
edhans
Super User
Super User

You need to remodel your data. You may have two data sources that have 2019 and 2020 in one source, and 2021 in another, but that is what Power Query is for. You combine those into a single FACT table so you don't have this issue. This will scale too. Another source for 2022? Just transform and combine in Power Query. 2023, 2024? Same.

If you use DAX to do this, it will keep getting worse. Can you imagine the DAX gymnastics you'd have to use to get it to combine 4-6 FACT tables that are really the same data just different years?



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Hi there, 

 

Thanks for your repsonse. It is not that the data is fro 2 sources, it is that the calcuation is differnet for past years than current years. So next year one cacluation will apply to the 2019, 2020, 2021 data, but 2022 will be different. Essentially it is becuase the full year calc is differnet to the ongoing monthly calc, however the result is being presented as the same measure.

 

Appreacite any more advice.

 

Thanks,

 

Alex

You need a single measure that will handle both. The measure can get the year from the filter context provided by the axis of your chart, then do the appropriate calculation for the current year vs past years. There is really no way for me to help further without some data and a model to play with. I'm not sure I could give a good answer with just the fact table of your sales. A link to a PBIX file with fake data would be very helpful.

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Hi,

 

Thanks again for your help. I have tried to pull out some data but there are so many tables and measures it all falls apart because of all the hierarchys, so have tried to break it down below in a table as I'd like the PivotTable to look (and thus the chart). 

 

Calc 1: As it is the end of the year it is full year revenue divided by December costs (which are an annual figure such as staff costs) and is used as the end of year benchmark so other dates are filtered out, calculated basically as follows: = sum(Costs[Amount]) / sum(Revenue[YTD Amount])

 

Calc 2 is different due to end of year revenue not being available and is: = (sum(Costs[Amount])/12)  /  sum(Revenue[MTD Amount])

 

The main difference is that as the costs being reported each month are annualised, for calc 2 they are divided by 12 and compared to one months revenue, whilst for the december values its just the full amount.

 

 

 Dec 19Dec 20Jan 21Feb 21Mar 21Apr 21May 21
Entity 1Calc 1Calc 1Calc 2Calc 2Calc 2Calc 2Calc 2
Entity 2Calc 1Calc 1Calc 2Calc 2Calc 2Calc 2Calc 2
Entity 3Calc 1Calc 1Calc 2Calc 2Calc 2Calc 2Calc 2
Entity 4Calc 1Calc 1Calc 2Calc 2Calc 2Calc 2Calc 2
Entity 5Calc 1Calc 1Calc 2Calc 2Calc 2Calc 2Calc 2
Entity 6Calc 1Calc 1Calc 2Calc 2Calc 2Calc 2Calc 2
Entity 7Calc 1Calc 1Calc 2Calc 2Calc 2Calc 2Calc 2
Entity 8Calc 1Calc 1Calc 2Calc 2Calc 2Calc 2Calc 2
Entity 9Calc 1Calc 1Calc 2Calc 2Calc 2Calc 2Calc 2
Entity 10Calc 1Calc 1Calc 2Calc 2Calc 2Calc 2Calc 2

 

I can't seem to find a way to get the measure to use a different calc for either:

 

1. When the month is December, use calc 1, if not use 2

2. When year is not current / isnt 2021 or similar use Calc 1 (it wouldnt matter that the non December months were out as they are never used for benchmarking in this way) .

 

Hopefully that explains it better than before, and thanks for looking.

 

Alex

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
Top Kudoed Authors