Forum Discussion
Advocate IIDAX Multiple column matches? Model attached
Hi carlenb please try measure below (include possible filtering on column Projekt)
Sales Total Measure =VAR __Table_sum =SUMMARIZE ('Sales2019-2022',[Sales date],[Supplier],[Supplier ID],[Article],"__SalesVolume", SUM ( [Sales volume] ))VAR __Result_sum =SUMX ( __Table_sum, [__SalesVolume] )RETURNIF (ISFILTERED ( DimProject[Projekt] ),__Result_sum,SUM ( 'Sales2019-2022'[Sales volume] ))
Community Championcarlenb Typically in these sorts of situations, you unpivot your columns so that your 5 columns become 2 columns, Attribute and Value. The Attibute becomes the column name and the values, well, the values.
carlenbThanks Greg_Deckler
I tried to unpivot and (almost) got it to work. It does return the correct value when I filter on projects, dates, suppliers etc. Yej! But if I leave it unfiltered it returns the wrong total sum, what am I missing? Please see below check in Excel (correct value) vs Power BI.
Power BI
- Greg_Deckler
carlenb Right, so after unpivoting your Sales Volume numbers are duplicated so a simple sum will return inflated values. You can fix this with a measure like below. I'm not 100% understanding of your data or why it is organized how it is so there may be other approaches but this will fix the sum for the unpivoted data as you have it:
Sales Total Measure = VAR __Table = SUMMARIZE('Table', [Sales date], [Supplier], [Supplie ID], [Article], "__SalesVolume", MAX([Sales volumn])) VAR __Result = SUMX(__Table, [__SalesVolume]) RETURN __Result- carlenb
Thanks Greg_Deckler for the patience. I get it to work for for each project, supplier, year etc. But when I select multiple projects the sum is off.
Example
The Power BI sales volume for all I projects is 27820 but it should be 38553, see picture below. I've linked the Power BI model and test data. Any ideas?
Power BI: https://drive.google.com/file/d/1XDaL5TBVejoCArJo_-dUs-QxVaTrRliO/view?usp=sharing
Expected output
Power BI output