Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Khushi
Frequent Visitor

In Powerbi how to get SUM of values present in measure

I have below data in table 

Id  Dispatch ID    In store     Send   
1411
14-11
27-10
2711
3811
3811
3911


Now i need distinct count of (id,dispatch id) which is send

In this case it should only choose where In store=1 and send =1
As Id 1 has Instore =-1 and shared also =1 Then this should be ignored

Total in card it should be Count of (2,(3,8),(3,9))=3

ID 1 is ignored as it has combination that it was not present in stored but then also send.

I tried couple of measure but all failed at certain point with different dataset.

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1686804086183.png

 

 

Expected result measure: =
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[Id], Data[Dispatch ID] ),
        "@count",
            COUNTROWS (
                CALCULATETABLE ( FILTER ( Data, Data[In store] * Data[Send] = 1 ) )
            ),
        "@countnotwant",
            COUNTROWS (
                CALCULATETABLE ( FILTER ( Data, Data[In store] * Data[Send] = -1 ) )
            )
    )
RETURN
    COUNTROWS ( FILTER ( _t, [@countnotwant] = BLANK () ) )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1686804086183.png

 

 

Expected result measure: =
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[Id], Data[Dispatch ID] ),
        "@count",
            COUNTROWS (
                CALCULATETABLE ( FILTER ( Data, Data[In store] * Data[Send] = 1 ) )
            ),
        "@countnotwant",
            COUNTROWS (
                CALCULATETABLE ( FILTER ( Data, Data[In store] * Data[Send] = -1 ) )
            )
    )
RETURN
    COUNTROWS ( FILTER ( _t, [@countnotwant] = BLANK () ) )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.