Forum Discussion
Need help with IF function on duplicate collums with different types
Hello :)
I am very new to Power BI and still learning.
I have been helped with most things on this forum from looking around, but iv faced an obstical i havent seen out there yet,
I need the % of shipmenst in total with just NI and just DT
And a % total with both NI and DT
These are types of shipments we get in our warehouse.
Please let me know if i need to post more information on the matter, first time poster
Hi,
I've created a sample demo file to showcase the measures you can use to accomplish your task.
Here is one of the measures - counting shipments with "NI" as the only value in Type column:
NI = CALCULATE( DISTINCTCOUNT(Data[Forsendelses nr]); FILTER( Data; CONTAINS( Data; Data[Forsendelses nr];[Forsendelses nr]; Data[Type];"NI" ) && NOT( CONTAINS( Data; Data[Forsendelses nr];[Forsendelses nr]; Data[Type];"DT" ) ) ) )
To count all shipments you can use the following measure:
Number of Shipments = CALCULATE(DISTINCTCOUNT(Data[Forsendelses nr]);ALL(Data))
Then calculation of "NI-only" shipments is straightforward:
% of NI = DIVIDE([NI];[Number of Shipments])
You can download a sample .pbix file here: http://blog.sqlgeek.pl/Download/DAX - Count with EXISTS.pbix.
2 Replies
Hi,
I've created a sample demo file to showcase the measures you can use to accomplish your task.
Here is one of the measures - counting shipments with "NI" as the only value in Type column:
NI = CALCULATE( DISTINCTCOUNT(Data[Forsendelses nr]); FILTER( Data; CONTAINS( Data; Data[Forsendelses nr];[Forsendelses nr]; Data[Type];"NI" ) && NOT( CONTAINS( Data; Data[Forsendelses nr];[Forsendelses nr]; Data[Type];"DT" ) ) ) )
To count all shipments you can use the following measure:
Number of Shipments = CALCULATE(DISTINCTCOUNT(Data[Forsendelses nr]);ALL(Data))
Then calculation of "NI-only" shipments is straightforward:
% of NI = DIVIDE([NI];[Number of Shipments])
You can download a sample .pbix file here: http://blog.sqlgeek.pl/Download/DAX - Count with EXISTS.pbix.