need urgent help with Power BI -Dax

[Anonymous]

Dear Community,

I need some help. I have attached a

sample PBIX file.

https://drive.google.com/file/d/1etxBn4Yn-U0qBL7zqVa0WjdFEWMbnX_F/view?usp=drive_link

Sample source Excel file

https://docs.google.com/spreadsheets/d/1W9mfauVaCmNKHyhXG2YFY-BCrXxtp_Ap/edit?usp=drive_link&ouid=11...

My requirement:

As you can see, for bike there are 5 **Distinct Users**.

So If it select Bike in the Slicer, I want all the other rows also to be shown, but for only those users who purchased bike.

For ex: the Bike was purchased by 5 users. How many of those 5 users also purchased Car, Cylce...etc..

and If I select Car, how many of those 7 users also purchased other products.

Could someone help me out 🙂 

Thank you

[Anonymous · Accepted solution]

Hi @Anonymous 

You can refer to the following solution.

1.Create a pname column in factorder table.

Pname = RELATED(DimProduct[PNAME])

2.Create the following measures

Counts =
VAR a =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( FactOrder ), [PID] IN VALUES ( DimProduct[PID] ) ),
        [CID]
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( FactOrder[CID] ),
        FactOrder[CID] IN a,
        CROSSFILTER ( DimProduct[PID], FactOrder[PID], NONE )
    )

Amounts =
VAR a =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( FactOrder ), [PID] IN VALUES ( DimProduct[PID] ) ),
        [CID]
    )
RETURN
    CALCULATE (
        SUM ( FactOrder[Amount] ),
        FactOrder[CID] IN a,
        CROSSFILTER ( DimProduct[PID], FactOrder[PID], NONE )
    )

3.Put the pname created above to the table visual and remove the pname of dimproduct, and put the measures to the visual. 

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Thank you so much Ahmed. It was indeed greatly helpful. 

Kudos to you!!

[Anonymous · Accepted solution]

Hi @Anonymous 

You can refer to the following solution.

1.Create a pname column in factorder table.

Pname = RELATED(DimProduct[PNAME])

2.Create the following measures

Counts =
VAR a =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( FactOrder ), [PID] IN VALUES ( DimProduct[PID] ) ),
        [CID]
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( FactOrder[CID] ),
        FactOrder[CID] IN a,
        CROSSFILTER ( DimProduct[PID], FactOrder[PID], NONE )
    )

Amounts =
VAR a =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( FactOrder ), [PID] IN VALUES ( DimProduct[PID] ) ),
        [CID]
    )
RETURN
    CALCULATE (
        SUM ( FactOrder[Amount] ),
        FactOrder[CID] IN a,
        CROSSFILTER ( DimProduct[PID], FactOrder[PID], NONE )
    )

3.Put the pname created above to the table visual and remove the pname of dimproduct, and put the measures to the visual. 

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It was indeed an art of master piece!!

It was greatly helpful. Thanks a bunch!!

1 quick question though: How to caculate the percentage out of total percentage for each row dynamically. Any suggestions please.

some thing like below, how can I fit it into the given dynamic calculation.

DIVIDE (SUM(FactOrder[Amount]), CALCULATE(SUM(FactOrder[Amount]), ALL(DimProduct[PNAME])),0)

[Anonymous]

Hi@Anonymous (w.r.t your Solution)

1 quick question please: Could you give me some directions to calculate the percentage of each row out of total selected amount, So that I can place that as 5th dynamic column. 

Static one:

DIVIDE (SUM(FactOrder[Amount]), CALCULATE(SUM(FactOrder[Amount]), ALL(DimProduct[PNAME])),0)

Thanks in Advance!

cc: @Ahmedx 

[Ahmedx]

pls try this

look at everything carefully, I changed the model and added a few measures