cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cassio
Frequent Visitor

Noob here, sorry to bother

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.Domanda_2.png

 

 

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!

1 ACCEPTED SOLUTION
zoloturu
Memorable Member
Memorable Member

@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

seller2.JPG


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

View solution in original post

6 REPLIES 6
zoloturu
Memorable Member
Memorable Member

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
Frequent Visitor

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
Memorable Member
Memorable Member

@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

seller2.JPG


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

View solution in original post

Cassio
Frequent Visitor

@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
Memorable Member
Memorable Member

@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
Frequent Visitor

@zoloturu 

 

You are the saviour of my day!

 

Thank you so much again for your help and advices.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!