Getting a 1 in all rows.

[Kent1341]

Hi,

This is one that really puzzles me. 

I have a table with my sales and want to know how significant a portion that is not selling. The granularity that I have is one row per product per store. So I want to calculate divide(shops that have sold the product, Shops in total) per product. It is a "Yes/No" question. So have they sold anything the value is 1 and have they not sold anything the value is 0.

So far I have calculated the products that sell. 

My problem is to get to the "Shops Total". It is OK to get to the 1 and 0, but when it starts to calculate I get a wrong result. (See below). The result in the row with 18 should be (18/24) = 75% and not (18/18). 

The problem is that in weeks without any sales of a product, there are no rows in my table that have the combination (Store / Product), if there was a row it would have sales, so this is my problem. I force in a "1" for the blank, but when it has to make the sumups, there is no blank, and the calculation is done on the rows that have sales without looking at the ones that have no sales.

Can anyone help me with this one? How do I get a result of 24 and not 18 in this calculation?

Br Kent

[lbendlin · Accepted solution]

To report on things that are not there you need to use disconnected tables and/or crossjoins

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[v-yiruan-msft · Accepted solution]

Hi @Kent1341 ,

You can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Kent1341]

Hi,

First of all - Sorry that it took a week, but I got a small distance to my goal - but not quite as long as I wanted. I got how many products/stores there are in total for each week. Great! It was in my relation where the relation between the store and the fact table and the relation between products and the fact table that was wrong. I had the cross filter direction set to "both" and not to "single" as it should be. So far so good!

But now I don't get what I want in the end. The SUM is wrong. I have made a small file where you can see the problem: https://oneclickinsight-my.sharepoint.com/:u:/p/kent/EYf530JMjKNGsofWlEjab_gBRd_7ha2S4AcnvbYXH5IXPA?...

I hope that you can help me!

Br Kent

[lbendlin · Accepted solution]

Not really clear what you are trying to achieve. What's the story you want to tell?

see attached.

[Kent1341]

OMG! This actually got me to the solution. 

Thanks a lot, Ibendlin. 

Be Kent

[v-yiruan-msft · Accepted solution]

Hi @Kent1341 ,

You can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[lbendlin · Accepted solution]

To report on things that are not there you need to use disconnected tables and/or crossjoins

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...