Forum Discussion

carlenb's avatar
carlenb
Icon for Advocate II rankAdvocate II
2 years ago
Solved

DAX 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...
  • some_bih's avatar
    some_bih
    2 years ago

    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] )
    RETURN
        IF (
            ISFILTERED ( DimProject[Projekt] ),
            __Result_sum,
            SUM ( 'Sales2019-2022'[Sales volume] )
        )

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
carlenb
Icon for Advocate II rankAdvocate II
2 years ago

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

 

Test data: https://docs.google.com/spreadsheets/d/1cJ-sHIwvC0pJjSYYznyVUzgGNrrF1mjn/edit?usp=sharing&ouid=113584471339137695632&rtpof=true&sd=true

 

Expected output 

 

Power BI output