Forum Discussion
carlenb
Advocate II
Advocate IIDAX Multiple column matches? Model attached
Hi, I have a Sales table from 2019 to 2022 with sales volume, sales date and suppliers. See model here: https://drive.google.com/file/d/1ie-tNNGnjq2jvL54EP1bJF2xWKNMK3AU/view?usp=sharing Pr...
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] ))
some_bih
Community Champion
Community ChampionHi carlenb
In test data, there is wrong sum for second listed project ID and please change MAX to SUM as marked below and you should get expected output.
carlenbAdvocate II
Advocate IIThanks some_bih
I updated from MAX to SUM in the DAX and this is what happens:
- Now the sum for each project is correct
- However the total sum for all or multiple projects (for example if you select project I & project H the total sum will not be correct. When I use MAX as suggested by Greg_Deckler it is the other way around, so the total sum for all projects is correct, but not for each project
See pictures below on what happens. Any ideas of how to get around this?
From test data, the correct sum for all projects
In Power BI using MAX for all projects (total is correct)
In Power BI using SUM for all projects (total is not correct)
In Power BI using SUM for specific projects (total is correct)
- some_bih
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] ))