Noob here, sorry to bother

[Cassio]

Good evening,

I'm really new to Power BI (and to any program of business intelligence) and I’m trying to learn on my own, so bear with me.

With data like these I would like to create a report where it is possible to search for “product” or “seller” and see graphs with the Quantity and the Import distributed over month. And I managed to do this.

The problem comes when I would like to see the difference month by month of Quantity and Import between, for example, Trony and Mediaworld. So, if I filter by product and search Trony and Mediaworld I would like a report that shows these data

Month	OrderedDIfference	Import Difference
Gen	1	4
Feb	2	13
Mar	0	-7
Apr	2	9
TOTAL	5	19

But most important I want that the difference is always calculated using Ordered and Import data from the Seller or Product I filtered!

I hope that everything is clear, feel free to ask and thanks so much in advice!

[zoloturu]

Hi @Cassio ,

I can help you with such task. Please clarify some requirements before.

Let's define how do you want to mark which two sellers to compare. I suggest you next ideas:

Option 1. 

We can write 2 measures via DAX language (one for Order and second for Import Diff) where we will define these two sellers and result will be working as needed. Once you need to compare other sellers you go to a DAX formula and manually changes their names.

Option 2.

We woud need to create 2 tables which will contains only column Seller with unique values (and no relationships should be done from them to other tables). Then we create two separate slicers based on each of these new tables. And the last step to write a DAX measure which will track selections from the slicers and will calculate Order and Import differences based on the slicers. (This trick is called Virtual Relationships).

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

[Cassio]

First of all, thanks so much for your fast answer.
About Option 1 is what I just came up to, with different measures made with Calculate over the (for example) Import and changing manually the filter with the company name.

I'm really curious about Option 2. Would you mind going deeper with explanation please?

[zoloturu · Accepted solution]

@Cassio ,

1. Create a DAX table Seller1 which will hold only unique values of a column Seller:

Seller1 = VALUES(Sales[Seller])

2.  Create a similar table to previous and name it as Seller2:

Seller2 = VALUES(Sales[Seller])

3. Create slicers based on Seller1 and Seller2

4. Create a measure to calculate OrderedDifference in your main table where variables __seller1 and __seller2 retrieving selections from the slicers :

OrderedDifference = 
VAR __seller1 =
    IF (
        HASONEVALUE ( Seller1[Seller] ),
        SELECTEDVALUE ( Seller1[Seller] ),
        "More than 1 value selected"
    )
VAR __seller2 =
    IF (
        HASONEVALUE ( Seller2[Seller] ),
        SELECTEDVALUE ( Seller2[Seller] ),
        "More than 1 value selected"
    )
RETURN
    IF (
        __seller1 = "More than 1 value selected" || __seller2 = "More than 1 value selected",
        
        "Select 1 value for each filter of seller",

        VAR __ordered1 =
            CALCULATE (
                SUM ( Sales[Quantity Ordered] ),
                FILTER ( 'Sales', 'Sales'[Seller] = __seller1 )
            )
        VAR __ordered2 =
            CALCULATE (
                SUM ( Sales[Quantity Ordered] ),
                FILTER ( 'Sales', 'Sales'[Seller] = __seller2 )
            )

        RETURN
            __ordered1 - __ordered2
    )

Result

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

[Cassio]

@zoloturu 

My Gosh amazing!!!! Thank you so much, It actually worked really well (changing previously commas into semi-columns)!!!! You really saved me!

2 further question:

-Which is the best way and place to learn more about DAX?

-If my seller's data in the main table changes, do I have to manually change the data also in the new tables Seller1 and Seller2? Is there a way to connect them?

And again thank you so much!

[zoloturu]

@Cassio ,

I'm happy to hear that. 

When you need to know more about DAX then mix of following resources can help you:

1) Get started - https://www.sqlbi.com/p/introducing-dax-video-course/ . It is a free video course from SQLBI team.

2) Tips and tricks - https://www.sqlbi.com/articles/

3) DAX handbook - https://dax.guide/ (also done by SQLBI team). But be careful and always double check and compare it with official Microsoft version - https://docs.microsoft.com/en-us/dax/ to be safe in understanding of DAX formulas.

And yes, because we used DAX calculated tables when creating Seller1 and Seller2 then it will be updated automatically once main table changed.

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

[Cassio]

@zoloturu 

You are the saviour of my day!

Thank you so much again for your help and advices.